SWITCH 语句

简单用法1:

SELECT CASE 3 WHEN 1 THEN '成功' WHEN 2 THEN '失败' ELSE '异常' END AS status; CASE 后面跟要被作为判断对象的字段 WHEN 后面跟判断条件 THEN 后面跟结果 ELSE 相当于 DEFAULT END 是语句结束语

简单用法2:

SELECT CASE WHEN false THEN '成功' WHEN true THEN '失败' ELSE '异常' END AS status; WHEN 后面跟布尔结果类型数据 THEN 后面跟结果 ELSE 相当于 DEFAULT END 是语句结束语 判断条件从前向后直到有一个WHEN为true时返回对应的结果,如果WHEN都不成立返回ELSE的结果.

用法1测试结果:

mysql> SELECT CASE 1 WHEN 1 THEN '成功' WHEN 2 THEN '失败' ELSE '异常' END AS status;
+--------+
| status |
+--------+
| 成功   |
+--------+
1 row in set


mysql> SELECT CASE 2
 WHEN 1 THEN '成功' WHEN 2 THEN '失败' ELSE '异常' END AS status;
+--------+
| status |
+--------+
| 失败   |
+--------+
1 row in set


mysql> SELECT CASE 3
 WHEN 1 THEN '成功' WHEN 2 THEN '失败' ELSE '异常' END AS status;
+--------+
| status |
+--------+
| 异常   |
+--------+
1 row in set


mysql>

用法1正常写法:

SELECT CASE [被判断字段] WHEN [条件1] THEN [条件1结果] WHEN [条件2] THEN [条件2结果] ELSE [都不符合时的结果] END AS [结果集别名] FROM [表名];

用法2测试结果:

mysql> SELECT CASE WHEN true THEN '成功' WHEN true THEN '失败' ELSE '异常' END AS status; +--------+ | status | +--------+ | 成功 | +--------+ 1 row in set

mysql> SELECT CASE WHEN false THEN '成功' WHEN true THEN '失败' ELSE '异常' END AS status; +--------+ | status | +--------+ | 失败 | +--------+ 1 row in set

mysql> SELECT CASE WHEN false THEN '成功' WHEN false THEN '失败' ELSE '异常' END AS status; +--------+ | status | +--------+ | 异常 | +--------+ 1 row in set

mysql>

用法2正常写法:

SELECT CASE WHEN [布尔类型结果] THEN [条件1结果] WHEN [布尔类型结果] THEN [条件2结果] ELSE [都不符合时的结果] END AS [结果集别名] FROM [表名];