blocks|key|1796152|text|CASE更像是一个switch语句。它有两种你可以使用的语法。第一个允许您使用所需的任何比较语句：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1796153|CASE+
++++WHEN+user_role+=+'Manager'+then+4
++++WHEN+user_name+=+'Tom'+then+27
++++WHEN+columnA+<>+columnB+then+99
++++ELSE+-1+--unknown
END|code-block|syntax|javascript|1796154|第二种风格适用于只检查一个值的情况，它更简洁一些：|1796155|CASE+user_role
++++WHEN+'Manager'+then+4
++++WHEN+'Part+Time'+then+7
++++ELSE+-1+--unknown
END|1796156|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html^0|0|4|0|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

<a href="https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html" rel="noreferrer"><code>CASE</code></a> is more like a switch statement. It has two syntaxes you can use. The first lets you use any compare statements you want:

<pre><code>CASE 
 WHEN user_role = 'Manager' then 4
 WHEN user_name = 'Tom' then 27
 WHEN columnA &lt;&gt; columnB then 99
 ELSE -1 --unknown
END
</code></pre>

The second style is for when you are only examining one value, and is a little more succinct:

<pre><code>CASE user_role
 WHEN 'Manager' then 4
 WHEN 'Part Time' then 7
 ELSE -1 --unknown
END
</code></pre>

blocks|key|2009919|text|MySQL中的CASE既是statement又是expression，两者的用法略有不同。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2009920|作为一条语句，CASE的工作方式与switch语句非常相似，并且在存储过程中非常有用，如文档(上面链接)中的示例所示：|2009921|DELIMITER+%7C

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;
++%7C|code-block|syntax|javascript|2009922|但是，作为表达式，它可以用在子句中：|2009923|SELECT+*
++FROM+employees
++ORDER+BY
++++CASE+title
++++++WHEN+"President"+THEN+1
++++++WHEN+"Manager"+THEN+2
++++++ELSE+3
++++END,+surname|2009924|此外，无论是作为语句还是表达式，第一个参数都可以省略，并且每个WHEN都必须接受一个条件。|2009925|SELECT+*
++FROM+employees
++ORDER+BY
++++CASE+
++++++WHEN+title+=+"President"+THEN+1
++++++WHEN+title+=+"Manager"+THEN+2
++++++ELSE+3
++++END,+surname|2009926|我之所以提供这个答案，是因为另一个答案没有提到CASE既可以用作语句，也可以用作表达式。它们之间的主要区别是语句形式以END+CASE结尾，而表达式形式仅以END结尾。|2009927|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.6/en/case.html|1|https://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html#operator_case^0|7|4|D|9|0|O|A|1|0|7|4|0|0|0|0|V|4|0|0|N|4|1N|8|26|3|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@$9|19|A|1A|1|1B]|$9|1C|A|1D|1|1E]]|E|$]]|$1|F|3|G|5|6|7|1F|8|@$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|1I|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1J|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1K|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1L|8|@$9|1M|A|1N|B|C]]|D|@]|E|$]]|$1|S|3|T|5|J|7|1O|8|@]|D|@]|E|$K|L]]|$1|U|3|V|5|6|7|1P|8|@$9|1Q|A|1R|B|C]|$9|1S|A|1T|B|C]|$9|1U|A|1V|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1W|8|@]|D|@]|E|$]]]|X|$Y|$5|Z|10|11|E|$12|13]]|14|$5|Z|10|11|E|$12|15]]]]

<code>CASE</code> in MySQL is both a <a href="https://dev.mysql.com/doc/refman/5.6/en/case.html" rel="noreferrer">statement</a> and an <a href="https://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html#operator_case" rel="noreferrer">expression</a>, where each usage is slightly different.

As a statement, <code>CASE</code> works much like a switch statement and is useful in stored procedures, as shown in this example from the documentation (linked above):

<pre><code>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;
 |
</code></pre>

However, as an expression it can be used in clauses:

<pre><code>SELECT *
 FROM employees
 ORDER BY
 CASE title
 WHEN "President" THEN 1
 WHEN "Manager" THEN 2
 ELSE 3
 END, surname
</code></pre>

Additionally, both as a statement and as an expression, the first argument can be omitted and each <code>WHEN</code> must take a condition.

<pre><code>SELECT *
 FROM employees
 ORDER BY
 CASE 
 WHEN title = "President" THEN 1
 WHEN title = "Manager" THEN 2
 ELSE 3
 END, surname
</code></pre>

I provided this answer because the other answer fails to mention that <code>CASE</code> can function both as a statement and as an expression. The major difference between them is that the statement form ends with <code>END CASE</code> and the expression form ends with just <code>END</code>.

blocks|key|1796470|text|我想要一个使用case的简单示例，我可以使用它，这甚至不需要一张桌子。这将返回奇数或偶数，具体取决于秒是奇数还是偶数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1796471|SELECT+CASE+MOD(SECOND(NOW()),2)+WHEN+0+THEN+'odd'+WHEN+1+THEN+'even'+END;|code-block|syntax|javascript|1796472|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>SELECT CASE MOD(SECOND(NOW()),2) WHEN 0 THEN 'odd' WHEN 1 THEN 'even' END;
</code></pre>

I know that SQL's <code>CASE</code> syntax is as follows:

<pre><code>CASE
 WHEN search_condition THEN statement_list
 [WHEN search_condition THEN statement_list] ...
 [ELSE statement_list]
END CASE
</code></pre>

However, I don't understand how this works, possibly because I'm thinking about it as about an <code>if</code> statement.

If I have a field in table <code>user_role</code>, for example, which contains names like "Manager", "Part Time" etc., how do I generate a field <code>role_order</code> 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.

How does MySQL CASE work?

MySQL

我知道SQL的CASE语法如下：CASE    WHEN search_condition THEN statement_list    [WHEN search_condition THEN statement_list] ...    [ELSE statement_list]END CASE然而，我不明白它是如何...

问MySQL CASE是如何工作的？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL CASE是如何工作的？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL CASE是如何工作的？
EN