switch語句

SWITCH 語句

簡單用法1:

SELECT CASE 3 WHEN 1 THEN '成功' WHEN 2 THEN '失敗' ELSE '異常' END AS status; CASE 后面跟要被作為判斷對象的字段 WHEN 后面跟判斷條件 THEN 后面跟結(jié)果 ELSE 相當(dāng)于 DEFAULT END 是語句結(jié)束語

簡單用法2:

SELECT CASE WHEN false THEN '成功' WHEN true THEN '失敗' ELSE '異常' END AS status; WHEN 后面跟布爾結(jié)果類型數(shù)據(jù) THEN 后面跟結(jié)果 ELSE 相當(dāng)于 DEFAULT END 是語句結(jié)束語 判斷條件從前向后直到有一個WHEN為true時返回對應(yīng)的結(jié)果,如果WHEN都不成立返回ELSE的結(jié)果.

用法1測試結(jié)果:

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結(jié)果] WHEN [條件2] THEN [條件2結(jié)果] ELSE [都不符合時的結(jié)果] END AS [結(jié)果集別名] FROM [表名];

用法2測試結(jié)果:

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 [布爾類型結(jié)果] THEN [條件1結(jié)果] WHEN [布爾類型結(jié)果] THEN [條件2結(jié)果] ELSE [都不符合時的結(jié)果] END AS [結(jié)果集別名] FROM [表名];