MySQL CASE如何工作? [英] How does MySQL CASE work?

问题描述

我知道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中有一个字段，其中包含诸如经理"，兼职"等名称，那么如何根据角色来生成具有不同编号的字段role_order.在此示例中，如果user_role ='经理'，则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

这篇关于MySQL CASE如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！