提问人：thanksforanyhelp7777 提问时间：11/3/2023 最后编辑：marc_sthanksforanyhelp7777 更新时间：11/3/2023 访问量：47
SQL - 合并 in 和 not in
SQL - Combining in and not in
问：
我有一个 SQL，它向我展示了包含 3 个特定部件的产品。我想添加一个条件，查询包含 A、B、C 部分但不包含 D 部分的产品：
现有 QUERY：
SELECT Product
FROM your_table_name
WHERE Part IN ('a', 'b', 'c')
GROUP BY Product
HAVING COUNT(DISTINCT Part) = 3;
子查询？
感谢您的帮助！
答：
0赞
JuSun Lee
11/3/2023
#1
其他方式。
AsIs:
SELECT Product
FROM your_table_name
WHERE Part IN ('a', 'b', 'c')
GROUP BY Product
HAVING COUNT(DISTINCT Part) = 3;
ToBe:
SELECT a.Product
FROM (
SELECT a.Product
, SUM(CASE WHEN a.Part = 'a' THEN 1 ELSE 0 END) AS A_YN
, SUM(CASE WHEN a.Part = 'b' THEN 1 ELSE 0 END) AS B_YN
, SUM(CASE WHEN a.Part = 'c' THEN 1 ELSE 0 END) AS C_YN
, SUM(CASE WHEN a.Part = 'd' THEN 1 ELSE 0 END) AS D_YN
FROM your_table_name a
GROUP BY a.Product
) a
WHERE 1=1
AND (A_YN + B_YN + C_YN) = 3
AND D_YN = 0
评论
0赞
thanksforanyhelp7777
11/4/2023
嗨，JuSun，此查询未返回所有值。我不知道为什么。不过，我真的很感谢你的努力。谢谢！
0赞
SmellyCat
11/3/2023
#2
您可以将 NOT EXISTS 与相关的嵌套查询一起使用吗？
SELECT Product
FROM your_table_name
WHERE Part IN ('a', 'b', 'c') AND NOT EXISTS (
SELECT * from your_table_name yt_inner
WHERE yt_inner.Part = 'd' and
yt_inner.Product = your_table_name.Product)
GROUP BY Product
HAVING COUNT(DISTINCT Part) = 3;
评论
and part <> 'D'count(distinct Part) = 3count(distinct Part)最小完整可验证示例（MCVE） 和为什么我应该提供 MCVEcase