1. 首页
  2. 博客
  3. MySQL CASE-WHEN-END语句

MySQL CASE-WHEN-END语句

在MySQL中，CASE语句类似于IF-THEN-ELSE语句的功能，并且有两种语法。

语法

MySQL中CASE语句的语法为：

1
2
3
4
5
6
7
8
9
10
11
12
13
CASE expression
WHEN value_1 THEN
{...statements to execute when expression equals value_1...}
[ WHEN value_2 THEN
{...statements to execute when expression equals value_2...} ]
[ WHEN value_n THEN result_n
{...statements to execute when expression equals value_n...} ]
[ ELSE
{...statements to execute when no values matched...} ]
END CASE;

或者

1
2
3
4
5
6
7
8
9
10
11
12
13
CASE
WHEN condition_1 THEN
{...statements to execute when condition_1 is TRUE...}
[ WHEN condition_2 THEN
{...statements to execute when condition_2 is TRUE...} ]
[ WHEN condition_n THEN
{...statements to execute when condition_n is TRUE...} ]
[ ELSE
{...statements to execute when all conditions were FALSE...} ]
END CASE;

区别是一个提供值value_1，判断条件在case后面，一个提供判断条件condition_1

参数 说明
expression 要与值列表（即：value_1，value_2，... value_n）进行比较的表达式或者值。
value_1, value_2, ... value_n 判断条件值
condition_1，condition2，... condition_n 判断条件（表达式）

MySQL CASE-WHEN-END语句示例

首先，我们来看一下CASE语句的第一种语法的示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DELIMITER //
CREATE FUNCTION IncomeLevel ( monthly_value INT )
RETURNS varchar(20)
BEGIN
DECLARE income_level varchar(20);
CASE monthly_value
WHEN 4000 THEN
SET income_level = 'Low Income';
WHEN 5000 THEN
SET income_level = 'Avg Income';
ELSE
SET income_level = 'High Income';
END CASE;
RETURN income_level;
END; //
DELIMITER ;

让我们看看第二种语法：

1
2
3
4
5
6
7
SELECT ITEM_NAME,
CASE
WHEN severity =0 THEN '正常'
WHEN severity =-1 THEN '未知'
ELSE '异常'
END AS STATE
FROM `monitors`

原文链接：https://codingdict.com/

热门标签