如何仅在1个条件中选择ID

Question

在显示的数据集中，我希望隔离或显示只在工作篮a、b或c中而不在d、e或f中的ID。我希望111222的ID符合该标准。我尝试过这个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

SQL默认情况下不区分大小写，但您的数据库可能更改了该标志；您也不需要在这里指定NOT IN子句，因为第一个子句已经忽略了('a', 'b', 'c')之外的任何内容。TABLE是一个SQL术语，也可能会导致查询出现某种问题；如果可以，请将其别名-如果不能，请将它封装在backticks中。

试试这个：

SELECT ID
FROM `table`
WHERE Workbasket IN ('a', 'b', 'c');

Answer 4

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')