mysql case when code example
Example 1: mysql case
select title, length,
CASE
when length < 50 then 'very short film'
when length < 90 then 'short film'
when length < 120 then 'medium length film'
when length > 120 then 'long film'
else 'unknown length'
END
from film;
select title, length,
CASE
when length < 50 then 'very short film'
when length < 90 then 'short film'
when length < 120 then 'medium length film'
when length > 120 then 'long film'
else 'unknown length'
END AS film_length
from film;
+
| title | length | film_length |
+
| ACADEMY DINOSAUR | 86 | short film |
| ACE GOLDFINGER | 48 | very short film |
| ADAPTATION HOLES | 50 | short film |
| AFFAIR PREJUDICE | 117 | medium length film |
| AFRICAN EGG | 130 | long film |
| AGENT TRUMAN | 169 | long film |
| AIRPLANE SIERRA | 62 | short film |
| AIRPORT POLLOCK | 54 | short film |
| ALABAMA DEVIL | 114 | medium length film |
| ALADDIN CALENDAR | 63 | short film |
| ALAMO VIDEOTAPE | 126 | long film |
| ALASKA PHANTOM | 136 | long film |
| ALI FOREVER | 150 | long film |
| ALICE FANTASIA | 94 | medium length film |
| ALIEN CENTER | 46 | very short film |
Example 2: mysql switch case
mysql> SELECT CASE WHEN 2>3 THEN 'this is true' ELSE 'this is false' END;
+
| CASE WHEN 2>3 THEN 'this is true' ELSE 'this is false' END |
+
| this is false |
+
Example 3: mysql switch case
SELECT
t2.company_name,
t2.expose_new,
t2.expose_used,
t1.title,
t1.status,
CASE status
when 'New' and t2.expose_new = 1 then 1
when 'New' and t2.expose_new = 2 then 2
when 'New' and t2.expose_new = 3 then 3
when 'Used' and t2.expose_used = 1 then 1
when 'Used' and t2.expose_used = 2 then 2
when 'Used' and t2.expose_used = 3 then 3
END as expose
FROM `products` t1
join manufacturers t2 on t2.id = t1.seller
where t1.seller = 4238
Example 4: mysql case when on date
SELECT
id,
CASE
WHEN date_activated > '2011-11-23 18:30:00' THEN 'after'
WHEN date_activated > '2010-01-20 00:00:00' THEN 'before'
ELSE 'not yet'
END AS date_note
FROM table1;
Example 5: case when postgres
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END