随笔 - 94  文章 - 0 评论 - 15 阅读 - 13万
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

复制代码
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 
             


 
复制代码

此类问题关键在于

  1. CASE WHEN 可以用于WHERE
  2. CASE WHEN 中可以使用AND 进行逻辑判断
  3. CASE WHEN 表达式最终由返回值

 

posted on   zooz  阅读(45423)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示