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`

在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