sql - 如何选择状态只在一个集合中的记录？

Question

假设我有下表Person 表 和 personStatus 表。

declare @Persons table
(PersonId int)

insert into @Persons select 10   
insert into @Persons select 11    
insert into @Persons select 12   
insert into @Persons select 13  
insert into @Persons select 14    

declare @PersonStatus table
(id int,statuss int)
insert into @PersonStatus (id,statuss) values(14,4)
insert into @PersonStatus (id,statuss) values(10,0)
insert into @PersonStatus (id,statuss) values(10,0)
insert into @PersonStatus (id,statuss) values(11,1)
insert into @PersonStatus (id,statuss) values(10,1)
insert into @PersonStatus (id,statuss) values(11,2)
insert into @PersonStatus (id,statuss) values(12,0)
insert into @PersonStatus (id,statuss) values(12,1)
insert into @PersonStatus (id,statuss) values(12,2)
insert into @PersonStatus (id,statuss) values(13,1)
insert into @PersonStatus (id,statuss) values(13,3)

现在我想找到他们的状态正好在这个集合{0,1,2}中的人ID结果是 ---> 10,11,12

怎么做？

Answer 1

SELECT  ID
FROM    PersonStatus
GROUP   BY ID
HAVING  SUM(CASE WHEN statuss IN (0, 1, 2) THEN 1 ELSE 0 END) >= 1 AND
        SUM(CASE WHEN statuss NOT IN (0, 1, 2) THEN 1 ELSE 0 END) = 0

• SQLFiddle Demo