MySQL CASE 是如何工作的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3389713/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does MySQL CASE work?
提问by JD Isaacks
I know that SQL's CASEsyntax is as follows:
我知道SQL的CASE语法如下：
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
However, I don't understand how this works, possibly because I'm thinking about it as about an ifstatement.
但是，我不明白这是如何工作的，可能是因为我将其视为一个if声明。
If I have a field in table user_role, for example, which contains names like "Manager", "Part Time" etc., how do I generate a field role_orderwith a different number depending on the role. In the case of this example, "if user_role = 'Manager' then role_order = 5".
user_role例如，如果我在 table 中有一个字段，其中包含“Manager”、“Part Time”等名称，我如何role_order根据角色生成具有不同编号的字段。在本例中，“如果 user_role = 'Manager' then role_order = 5”。
Please note I am looking for a teach a man how to fishanswer rather than give a man a fishanswer.
请注意，我正在寻找教一个人如何钓鱼的答案，而不是给一个人一个鱼的答案。
回答by RedFilter
CASEis more like a switch statement. It has two syntaxes you can use. The first lets you use any compare statements you want:
CASE更像是一个 switch 语句。它有两种您可以使用的语法。第一个允许您使用任何您想要的比较语句：
CASE
WHEN user_role = 'Manager' then 4
WHEN user_name = 'Tom' then 27
WHEN columnA <> columnB then 99
ELSE -1 --unknown
END
The second style is for when you are only examining one value, and is a little more succinct:
第二种样式适用于您只检查一个值的情况，并且更加简洁：
CASE user_role
WHEN 'Manager' then 4
WHEN 'Part Time' then 7
ELSE -1 --unknown
END
回答by Svip
CASEin MySQL is both a statementand an expression, where each usage is slightly different.
CASE在 MySQL 中既是语句又是表达式，两者的用法略有不同。
As a statement, CASEworks much like a switch statement and is useful in stored procedures, as shown in this example from the documentation (linked above):
作为语句，它的CASE工作方式与 switch 语句非常相似，并且在存储过程中很有用，如文档中的此示例所示（上面链接）：
DELIMITER |
CREATE PROCEDURE p()
BEGIN
DECLARE v INT DEFAULT 1;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN -- Do other stuff
END;
END CASE;
END;
|
However, as an expression it can be used in clauses:
但是，作为表达式，它可以在子句中使用：
SELECT *
FROM employees
ORDER BY
CASE title
WHEN "President" THEN 1
WHEN "Manager" THEN 2
ELSE 3
END, surname
Additionally, both as a statement and as an expression, the first argument can be omitted and each WHENmust take a condition.
此外，无论是作为语句还是作为表达式，第一个参数都可以省略，并且每个参数都WHEN必须带一个条件。
SELECT *
FROM employees
ORDER BY
CASE
WHEN title = "President" THEN 1
WHEN title = "Manager" THEN 2
ELSE 3
END, surname
I provided this answer because the other answer fails to mention that CASEcan function both as a statement and as an expression. The major difference between them is that the statement form ends with END CASEand the expression form ends with just END.
我提供了这个答案，因为另一个答案没有提到CASE它既可以作为语句也可以作为表达式。它们之间的主要区别在于语句形式以 结尾，END CASE而表达式形式以 结尾END。
回答by zzapper
I wanted a simple example of the use of case that I could play with, this doesn't even need a table. This returns odd or even depending whether seconds is odd or even
我想要一个我可以玩的案例用例的简单示例，这甚至不需要桌子。这返回奇数或偶数取决于秒是奇数还是偶数
SELECT CASE MOD(SECOND(NOW()),2) WHEN 0 THEN 'odd' WHEN 1 THEN 'even' END;
