在where条件中使用CASE WHEN 语句

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 表达式最终由返回值