mysql case的语法

测试表：team

第一种语法：

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

mysql> select * from team;
+------+
| name |
+------+
| a    |
| b    |
| c    |
| d    |
+------+
4 rows in set (0.00 sec)

mysql> select case name when 'a' then 'aaa' when 'b' then 'bbb'  when 'c' then 'ccc' when 'd' then 'ddd' end from team;
+------------------------------------------------------------------------------------------------+
| case name when 'a' then 'aaa' when 'b' then 'bbb'  when 'c' then 'ccc' when 'd' then 'ddd' end |
+------------------------------------------------------------------------------------------------+
| aaa                                                                                            |
| bbb                                                                                            |
| ccc                                                                                            |
| ddd                                                                                            |
+------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select case name when 'a' then 'aaa' when 'b' then 'bbb'  when 'c' then 'ccc' when 'd' then 'ddd' else 'eee' end from team; 
+-----------------------------------------------------------------------------------------------------------+
| case name when 'a' then 'aaa' when 'b' then 'bbb'  when 'c' then 'ccc' when 'd' then 'ddd' else 'eee' end |
+-----------------------------------------------------------------------------------------------------------+
| aaa                                                                                                       |
| bbb                                                                                                       |
| ccc                                                                                                       |
| ddd                                                                                                       |
+-----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

 

第二种语法：

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

 

mysql> select (case when name='a' then 'aaa' when name='b' then 'bbb' when name='c' then 'ccc' when name='d' then 'ddd' else 'ccc' end) alias from team;
+-------+
| alias |
+-------+
| aaa   |
| bbb   |
| ccc   |
| ddd   |
+-------+
4 rows in set (0.00 sec)

 

转载于:https://www.cnblogs.com/cqdxwjd/p/9919123.html