《Mysql应用mysql内置函数case用法介绍》要点：
本文介绍了Mysql应用mysql内置函数case用法介绍，希望对您有用。如果有疑问，可以联系我们。
导读：本节内容：mysql内置函数case使用介绍mysql对case函数的解释：
mysql> ? caseMany help items for your request exist.To make a...
本节内容：
mysql内置函数case使用介绍MYSQL教程
mysql对case函数的解释：
MYSQL教程
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.
URL: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.htmlMYSQL教程
例子:
MYSQL教程
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
例1：
MYSQL教程
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
如果case后面的表达式和when中的值相等,则返回相对应then后的值,否则返回else的值.MYSQL教程
例2：
MYSQL教程
mysql> SELECT CASE BINARY 'B'
-> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL
这个例子和上面的类似,只不过没有else值,返回nullMYSQL教程
例3：
MYSQL教程
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
如果case后面的值为真,返回then值,否则返回else值.MYSQL教程
案例：统计各班级中的及格与不及格人数
MYSQL教程
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)
SQL语句：
MYSQL教程
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)
以上通过实例介绍了mysql内置函数case的用法,希望对大家有所赞助.MYSQL教程
欢迎参与《Mysql应用mysql内置函数case用法介绍》讨论，分享您的想法，维易PHP学院为您提供专业教程。
转载请注明本页网址：
http://www.vephp.com/jiaocheng/12028.html
