在语句中包含多个值

Is there a way to make a CASE statement with an IN clause?

有办法用IN子句来陈述情况吗?

SELECT
CASE c.Number
IN ('1121231','31242323') THEN 1
IN ('234523','2342423') THEN 2
END AS Test
FROM tblClient c

3 个解决方案

#1

Yes. You need to use the "Searched" form rather than the "Simple" form of the CASE expression

是的。您需要使用“搜索”形式，而不是CASE表达式的“简单”形式

SELECT CASE
         WHEN c.Number IN ( '1121231', '31242323' ) THEN 1
         WHEN c.Number IN ( '234523', '2342423' ) THEN 2
       END AS Test
FROM   tblClient c

#2

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的建议相同的执行计划，所以更重要的是你要怎么写。

#3

If you have more numbers or if you intend to add new test numbers for CASE then you can use a more flexible approach:

如果您有更多的数字，或者您打算为案例添加新的测试数字，那么您可以使用更灵活的方法:

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.

另外，您可以使用常规表代替表变量。

#1