MySQL CASE 是如何工作的?
我知道SQL的CASE语法如下:
I know that SQL's CASE syntax is as follows:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
但是，我不明白这是如何工作的，可能是因为我将其视为一个 if 语句.
However, I don't understand how this works, possibly because I'm thinking about it as about an if statement.
如果我在表 user_role 中有一个字段，例如，其中包含Manager"、Part Time"等名称，我如何生成字段 role_order 根据角色使用不同的数字.在本例中，如果 user_role = 'Manager' then role_order = 5".
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_order with a different number depending on the role. In the case of this example, "if user_role = 'Manager' then role_order = 5".
请注意，我正在寻找一个教一个人如何钓鱼的答案，而不是给一个人一条鱼的答案.
Please note I am looking for a teach a man how to fish answer rather than give a man a fish answer.
推荐答案
CASE 更像是一个 switch 语句.它有两种您可以使用的语法.第一个让你可以使用任何你想要的比较语句:
CASE is more like a switch statement. It has two syntaxes you can use. The first lets you use any compare statements you want:
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
相关文章