MySQL CASE-WHEN-END语句

时间:2019-02-04 12:52:32  来源:igfitidea点击:

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

语法

MySQL中CASE语句的语法为:

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;

或者

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语句的第一种语法的示例:

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 ;

让我们看看第二种语法:

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