mysql - 如何比较两组

Question

我需要获取所有 aid 的值 cid != 40 和 cid= 39(多对多连接)

我已经准备了两个查询，但我不知道如何从第一个中排除第二个

SELECT DISTINCT aid FROM ac_branch WHERE aid IN (SELECT aid FROM ac_branch WHERE cid = 39);

SELECT DISTINCT aid FROM ac_branch WHERE aid IN (SELECT aid FROM ac_branch WHERE cid != 40);

Answer 1

看起来您实际上非常接近，这就是您要找的东西吗？

SELECT DISTINCT aid 
FROM ac_branch 
WHERE cid = 39 
   AND aid NOT IN (SELECT aid FROM ac_branch WHERE cid = 40)
;

---

或者，您也可以不使用子查询，如下所示:

SELECT aid 
FROM ac_branch
WHERE cid IN (39, 40)
GROUP BY aid
HAVING SUM(CASE WHEN cid=39 THEN 1 ELSE -1 END) = 1
;

不过，这假设对于给定的援助值只能有 39 和 40 的单个条目。例如，如果有两个 aid=1 和 cid=39 的条目，而没有 cid=4 的条目，则 aid=1 不会出现(因为总和为 2)。类似地，一名有两个 39 分和一个 40 分的助攻也将包含在结果中。

---

第三个选项涉及 JOIN

SELECT DISTINCT acb39.aid
FROM ac_branch AS acb39
LEFT JOIN ac_branch AS acb40
   ON acb39.aid = acb40.aid AND acb40.cid = 40
WHERE acb1.cid = 39
   AND acb40.aid IS NULL
;