带有案例的 SQL Server WHERE IN [英] Sql Server WHERE IN with CASE

问题描述

我们如何从 WHERE 子句中的 CASE 指定值的范围?
我的这个查询失败了

How can we specify range of values from CASE inside WHERE clause?
This query of mine fails

declare @ProductType int

select * from Products 
where ProductLine in 
(
    case @ProductType
        when 1 then ('TVs')
        when 2 then ('Handsets', 'Mobiles') --fails here
        else ('Books')
    end
)

这也行不通:

declare @ProductType int

select * from Products 
where (case @ProductType 
             when 1 then (ProductLine = 'TVs')
             when 2 then (ProductLine in  ('Handsets', 'Mobiles'))
             else (ProductLine = 'Books')
       end)

推荐答案

你不能这样做 - 你需要把它拆分成几个检查:

You cannot do that - you need to split it to several checks:

WHERE (ProductLine = 'TVs' AND @ProductType = 1)
   OR (ProductLine IN ('Handsets', 'Mobiles') AND @ProductType = 2)
   OR (ProductLine = 'Books' AND @ProductType NOT IN (1, 2))

这篇关于带有案例的 SQL Server WHERE IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！