~~~~~~~~~SQL语句关于Where中IN后用Case，马上给分！~~~~~~~

[hxm20003]

status  in (case type_order when 'A' then 30,50 when 'B' then  20,30 end)

status是int,这样写报错，应该如何写？

[Andy__Huang]

引用 2 楼 szm341 的回复:

case返回的是一个确定的标量值，30,50这显然不是一个值 select * from tb where (type_order='a' and status in(30,50)) or (type_order='b' and status in(20,30) )

+10086

[發糞塗牆貮號]

select * from tb where type_order='a' and status in(30,50) union all select * from tb where type_order='b' and status in(20,30)

[szm341]

case返回的是一个确定的标量值，30,50这显然不是一个值 select * from tb where (type_order='a' and status in(30,50)) or (type_order='b' and status in(20,30) )

[發糞塗牆]

30，50是字符串，不是int

[LongRui888]

引用 楼主 hxm20003 的回复:

status  in (case type_order when 'A' then 30,50 when 'B' then  20,30 end)

status是int,这样写报错，应该如何写？

把语句改一下，试试：

and
(case type_order when 'A' then ',30,50,' 
                 when 'B' then  ',20,30,' 
 end) like '%,'+cast(status as varchar)+',%' 

[-Tracy-McGrady-]

where 1=1
and (case type_order when 'A' then status in(30,50) when 'B' then  status in(20,30) end)

[xiongxyt2]

引用 2 楼 szm341 的回复:

case返回的是一个确定的标量值，30,50这显然不是一个值 select * from tb where (type_order='a' and status in(30,50)) or (type_order='b' and status in(20,30) )

++