mysql - 如何使用 SQL 获取两个表的所有 3 个组件的计数？

Question

假设我想为两个表 A 和 B 查找所有在 A 中但不在 B 中的记录的计数、在 A 和 B 中的所有记录以及在 B 中但不在 A 中的所有记录。我不需要实际记录，只需要所有 3 个组件的计数(想想维恩图)。

例如，当我说 A 和 B 中的记录时，我指的是对四个变量(例如 ID、年、月、日)具有相同值的所有记录的计数。

是否有一个时髦的查询可以有效地返回这些计数？

Answer 1

对于 A 和 B 中的所有内容，这是一个简单的 JOIN:

SELECT COUNT(*)
FROM A
JOIN B ON A.ID = B.ID AND A.Year = B.Year AND A.Month = B.Month AND A.Day = B.Day

请注意，这假设组合 (ID, Year, Month, Day) 在每个表中都是唯一的；如果有重复项，它将计算等效项之间的所有叉积。如果 ID 是表中的唯一键，那应该不是问题。

对于所有不在 B 中的 A，使用 LEFT JOIN:

SELECT COUNT(*)
FROM A
LEFT JOIN B ON A.ID = B.ID AND A.Year = B.Year AND A.Month = B.Month AND A.Day = B.Day
WHERE B.ID IS NULL

对于所有不在 A 中的 B，做同样的事情，但颠倒 A 和 B 的角色:

SELECT COUNT(*)
FROM B
LEFT JOIN A ON A.ID = B.ID AND A.Year = B.Year AND A.Month = B.Month AND A.Day = B.Day
WHERE A.ID IS NULL

您可以将前两个组合成一个查询:

SELECT SUM(B.ID IS NOT NULL) AS A_and_B_count, SUM(B.ID IS NULL) AS A_not_B_count
FROM A
LEFT JOIN B ON A.ID = B.ID AND A.Year = B.Year AND A.Month = B.Month AND A.Day = B.Day

但我认为不可能将第三个查询包含在其中。这将需要 FULL OUTER JOIN，而 MySQL 没有。

对于所有这些查询，请确保您正在比较的列中至少有一个具有索引，否则这将非常慢；越多越好。尽管如果它们中的任何一个是唯一的(例如 ID)字段，那应该就足够了。