Using the case when statement in the Where condition

CREATE TABLETb_test_report (IDint Identity, Stateidint, UserIDint, usernamevarchar( -))GoINSERT  intoTb_test_report (Stateid,userid,username)VALUES( -,1,'a')INSERT  intoTb_test_report (Stateid,userid,username)VALUES(Ten,2,'b')INSERT  intoTb_test_report (Stateid,userid,username)VALUES( -,2,'b')INSERT  intoTb_test_report (Stateid,userid,username)VALUES(Ten,1,'a')INSERT  intoTb_test_report (Stateid,userid,username)VALUES(Ten,1,'a')INSERT  intoTb_test_report (Stateid,userid,username)VALUES(Ten,3,'C')INSERT  intoTb_test_report (Stateid,userid,username)VALUES(Ten,3,'C')INSERT  intoTb_test_report (Stateid,userid,username)VALUES( -,1,'C')INSERT  intoTb_test_report (Stateid,userid,username)VALUES(Ten,1,'a')INSERT  intoTb_test_report (Stateid,userid,username)VALUES(Ten,1,'A')INSERT  intoTb_test_report (Stateid,userid,username)VALUES( -,1,'a')INSERT  intoTb_test_report (Stateid,userid,username)VALUES( -,1,'b')INSERT  intoTb_test_report (Stateid,userid,username)VALUES( -,1,'C')Go/** The known statement SELECT * from Tb_test_report where Stateid in (10,20) requires adding a condition (join and OR OR or expression) after the statement to query out, all Stateid = 10 and (s Records of tateid=20 and UserID = 2)*/ SELECT * fromTb_test_reportWHEREStateidinch(Ten, - )        and( Case                whenStateid=  -                     andUserid<> 2  Then 0               ELSE 1             END)= 1               

The key to this type of problem is

1. Case when can be used in where
2. You can use and to make logical judgments in case
3. Case-when expression is ultimately returned by the return value

Using the case when statement in the Where condition