SQL 具有多个值的 CASE IN 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7449509/
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
CASE IN statement with multiple values
提问by mameesh
Is there a way to make a CASE statement with an IN clause?
有没有办法用 IN 子句制作 CASE 语句？
SELECT
CASE c.Number
IN ('1121231','31242323') THEN 1
IN ('234523','2342423') THEN 2
END AS Test
FROM tblClient c
回答by Martin Smith
回答by Guffa
You can return the same value from several matches:
您可以从多个匹配项中返回相同的值：
SELECT
CASE c.Number
WHEN '1121231' THEN 1
WHEN '31242323' THEN 1
WHEN '234523' THEN 2
WHEN '2342423' THEN 2
END AS Test
FROM tblClient c
This will probably result in the same execution plan as Martins suggestion, so it's more a matter of how you want to write it.
这可能会导致与 Martins 建议相同的执行计划，因此更多的是您想如何编写它。
回答by Lukasz Szozda
The question is specific to SQL Server, but I would like to extend Martin Smith's answer.
该问题特定于 SQL Server，但我想扩展Martin Smith 的回答。
SQL:2003 standard allows to define multiple values for simple case expression:
SQL:2003 标准允许为简单的 case 表达式定义多个值：
SELECT CASE c.Number
WHEN '1121231','31242323' THEN 1
WHEN '234523','2342423' THEN 2
END AS Test
FROM tblClient c;
It is optional feature: Comma-separated predicates in simple CASE expression“ (F263).
它是可选功能：简单 CASE 表达式中的逗号分隔谓词“（F263）。
Syntax:
句法：
CASE <common operand>
WHEN <expression>[, <expression> ...] THEN <result>
[WHEN <expression>[, <expression> ...] THEN <result>
...]
[ELSE <result>]
END
As for know I am not aware of any RDBMS that actually supports that syntax.
至于知道我不知道任何实际支持该语法的 RDBMS。
回答by Bogdan Sahlean
If you have more numbers or if you intend to add new test numbers for CASEthen you can use a more flexible approach:
如果您有更多编号，或者您打算为其添加新的测试编号，CASE则可以使用更灵活的方法：
DECLARE @Numbers TABLE
(
Number VARCHAR(50) PRIMARY KEY
,Class TINYINT NOT NULL
);
INSERT @Numbers
VALUES ('1121231',1);
INSERT @Numbers
VALUES ('31242323',1);
INSERT @Numbers
VALUES ('234523',2);
INSERT @Numbers
VALUES ('2342423',2);
SELECT c.*, n.Class
FROM tblClient c
LEFT OUTER JOIN @Numbers n ON c.Number = n.Number;
Also, instead of table variable you can use a regular table.
此外，您可以使用常规表代替表变量。
