如何仅根据 1 个条件选择 ID

Question

在显示的数据集中，我希望隔离或显示仅在工作篮 a、b 或 c 中但不在 d、e 或 f 中的 ID。我希望 ID 111222 符合该标准。我尝试过这个 SQL，但没有成功。有什么建议吗？

select id
from table
where workbasket in ('a','b','c') and workbasket not in ('d','e','f')

Workbasket  ID
a           111222
a           123456
b           987654 
c           112333
d           123456 
e           987654
f           112333

Answer 1

select id
from table
where workbasket in ('a','b','c')

EXCEPT

select id
from table
where workbasket in ('d','e','f')

Answer 2

您还可以使用 NOT EXISTS 子查询

select id
from table t1
where workbasket in ('a','b','c') 
and NOT EXISTS 
(
 select *
 from table t2
 where t1.id = t2.id
 AND t2.workbasket in ('d','e','f') 
)

Answer 3

WITH CTE(Workbasket, ID) AS
(
   SELECT 'A',123456 UNION ALL
   SELECT 'A',111222 UNION ALL
   SELECT 'B',987654 UNION ALL
   SELECT 'C',112333 UNION ALL
   SELECT 'D',123456 UNION ALL
   SELECT 'E',987654 UNION ALL
   SELECT 'F',112333
)
SELECT C.ID
FROM CTE AS C
GROUP BY C.ID
HAVING MAX(C.Workbasket) IN('A','B','C')