問題描述
where 子句中使用等於和 IN 的 CASE 語句 (CASE Statement in where clause using equal to and IN)
WHERE CONDITION1='ABC'
AND Status =
CASE @Option
WHEN 1 THEN 'True'
WHEN 2 THEN 'False'
WHEN 3 THEN NULL
WHEn 4 THEN **IN ('True', 'False', NULL)**
END
How do I write a query where my first options match directly using = but my last option needs an IN
The above query gives error, but I want something similar to it, which I am not able to find out.
‑‑‑‑‑
參考解法
方法 1:
A CASE statement can't return a set of values... but this query should give you the same results:
WHERE CONDITION1='ABC'
AND Status =
CASE
WHEN 1 THEN 'True'
WHEN 2 THEN 'False'
WHEN 3 THEN NULL
WHEN 4 THEN Status
END
Also, note that unless you have ANSI_NULLS OFF, Status will never = NULL... you would need to use IS NULL for this comparison, and you'd need to forgo the CASE statement altogether.
方法 2:
Skip the CASE statement and use OR. And as per ANSI standard don't compare with NULL:
WHERE CONDITION1='ABC'
AND ((@Option = 1 AND Status = 'True') OR
(@Option = 2 AND Status = 'False') OR
(@Option = 3 AND Status IS NULL) OR
(@Option = 4 AND (Status IS NULL OR Status IN ('True', 'False'))))
(by RRMN、Michael Fredrickson、PinnyM)