如何使用“或” MySQL CASE表达式中的条件？ [英] How can I use "OR" condition in MySQL CASE expression?

问题描述

我有一个包含CASE表达式语句的过程，如下所示：

I have a procedure that contains CASE expression statement like so:

BEGIN
....
WHILE counter < total DO
....

 CASE ranking
  WHEN 1 OR 51 OR 100 OR 167 THEN SET
   project_name = 'alpha';
  WHEN 2 THEN SET
   project_name = 'beta';
  WHEN 10 OR 31 OR 40 OR 61 THEN SET
   project_name = 'charlie';
  ....
  ELSE SET
   project_name = 'zelta';
 END CASE;

 INSERT INTO project (id, name) VALUES (LAST_INSERT_ID(), project_name);
 SET counter = counter + 1;

END WHILE;
END
$$
DELIMITER ;

当我调用上述过程时， OR 语句被完全跳过或仅匹配列表中的第一个项目。我做错了什么？

When I call the above procedure, cases with OR statements are either skipped completely or only the first item in the list is matched. What am I doing wrong?

推荐答案

CASE ranking
  WHEN 1 THEN 'alpha' 
  WHEN 2 THEN 'beta'  
  WHEN 10 THEN 'charlie' 
  ELSE 'zelta' 
END CASE;

您可以使用其中一个表示，但不能混合使用。

You can use one of expresions that WHEN has, but you cannot mix both of them.

1）WHEN when_expression
当使用简单的CASE格式时，是比较input_expression的简单表达式。 when_expression是任何有效的表达式。 input_expression和每个when_expression的数据类型必须相同或必须是隐式转换。

1) WHEN when_expression Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

2）WHEN Boolean_expression
在使用搜索CASE格式。 Boolean_expression是任何有效的布尔表达式。

2) WHEN Boolean_expression Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

您可以编程：

1）

 CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'

2）

CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END

但是在任何情况下，您都可以期望在布尔表达式中比较变量排名。

But in any case you can expect that the variable ranking is going to be compared in a boolean expresion.

http：// msdn.microsoft.com/en-us/library/ms181765.aspx

这篇关于如何使用“或” MySQL CASE表达式中的条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！