CREATE TABLE TB_Test_Report ( id int identity, stateid int, userid int, username varchar(64) ) go INSERT 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
此类问题关键在于
- CASE WHEN 可以用于WHERE
- CASE WHEN 中可以使用AND 进行逻辑判断
- CASE WHEN 表达式最终由返回值
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步