在where条件中使用CASE WHEN 语句

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

CREATE TABLE TB_Test_Report
(
id int identity,
stateid int,
userid int,
username varchar(64)
)goINSERT INTO TB_Test_Report (stateid,userid,username) VALUES (20,1,'a')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (10,2,'b')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (20,2,'b')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (10,1,'a')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (10,1,'a')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (10,3,'c')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (10,3,'c')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (20,1,'c')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (10,1,'a')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (10,1,'A')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (30,1,'a')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (30,1,'b')
INSERT INTO TB_Test_Report (stateid,userid,username) VALUES (30,1,'c')
go/**
已知语句 select * from TB_Test_Report where stateid in (10,20)
要求在语句后面增加条件(加入 and 或者 or 或者表达式)
查询出，所有stateid =10 和 (stateid=20 并且userid = 2) 的记录
*/SELECT *
FROM TB_Test_Report
WHERE stateid IN ( 10, 20 )
AND ( CASE
WHEN stateid = 20
AND userid <> 2 THEN 0
ELSE 1
END ) = 1