CASE IN statement with multiple values
Is there a way to make a CASE st开发者_运维技巧atement with an IN clause?
SELECT
CASE c.Number
IN ('1121231','31242323') THEN 1
IN ('234523','2342423') THEN 2
END AS Test
FROM tblClient c
Yes. You need to use the "Searched" form rather than the "Simple" form of the CASE
expression
SELECT CASE
WHEN c.Number IN ( '1121231', '31242323' ) THEN 1
WHEN c.Number IN ( '234523', '2342423' ) THEN 2
END AS Test
FROM tblClient c
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.
The question is specific to SQL Server, but I would like to extend Martin Smith's answer.
SQL:2003 standard allows to define multiple values for simple case expression:
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).
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.
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.
精彩评论