mysql内置函数case用法介绍

mysql> ? case
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CASE OPERATOR
   CASE STATEMENT
mysql> ? case operator
Name: 'CASE OPERATOR'
Description:
Syntax:
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] END
The first version returns the result where value=compare_value. The
second version returns the result for the first condition that is true.
If there was no matching result value, the result after ELSE is
returned, or NULL if there is no ELSE part.
 

mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
 

mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL
 

mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
 

mysql> select * from student;
+----+-------+-------+-------+
| id | class | name  | score |
+----+-------+-------+-------+
|  1 |     1 | name1 |    50 |
|  2 |     1 | name2 |    30 |
|  3 |     2 | name1 |    60 |
|  4 |     1 | name2 |    30 |
|  5 |     2 | name1 |    60 |
|  6 |     1 | name2 |    70 |
|  7 |     2 | name1 |    60 |
|  8 |     1 | name2 |    70 |
|  9 |     2 | name1 |    60 |
| 10 |     3 | name2 |    70 |
| 11 |     2 | name1 |    60 |
| 12 |     3 | name2 |    20 |
| 13 |     2 | name1 |    60 |
| 14 |     3 | name2 |    20 |
+----+-------+-------+-------+
14 rows in set (0.00 sec)

mysql> select class,count(case when score>=60 then 1 end) as '及格人数',count(case when score<60 then 1 end) as '不及格人数',count(*) as '总人数' from student group by class;
+-------+--------------+-----------------+-----------+
| class | 及格人数     | 不及格人数      | 总人数    |
+-------+--------------+-----------------+-----------+
|     1 |            2 |               3 |         5 |
|     2 |            6 |               0 |         6 |
|     3 |            1 |               2 |         3 |
+-------+--------------+-----------------+-----------+
3 rows in set (0.00 sec)