sql - 在 WHERE 字段中使用 CASE

Question

以下不是“正确的方法”，而是我要实现的目标的演示:

SELECT
   [...]
FROM
   [...]
WHERE condition1 
  AND condition2 
  AND field IN CASE (@type)
                 WHEN 1 THEN (SELECT subquery #1)
                 WHEN 2 THEN (SELECT subquery #2)
                 WHEN 3 THEN (SELECT subquery #3)
               END

意思是，我想为参数 @type 的每个不同值创建一个不同的子查询。有什么好的方法吗？

Answer 1

试试这个

SELECT
   [...]
FROM
   [...]
WHERE
   condition1 AND condition2 AND 
   ((@type = 1 and field IN (SELECT subquery #1))
    OR (@type = 2 and field IN (SELECT subquery #2))
    OR (@type = 3 and field IN (SELECT subquery #3))
   )