SQL选择联合中的多个项

Question

我正在执行几个选择，并希望计算重叠。示例：

SELECT id FROM foo WHERE ...
SELECT id FROM bar WHERE ...
SELECT id FROM baz WHERE ...

分别调用这些查询a、b和c。假设a给出（1,2,3,4,5），b给出（1,3,5）和c给出（4,5,6）。我想取这些的并集，计算乘法。对于上面的例子，我正在寻找的结果是

id | multiplicity
-----------------
1  | 2
2  | 1
3  | 2
4  | 2
5  | 3
6  | 1

如何在一个查询中在MySQL5中做到这一点？（a、b和c部分可以是纯选择或存储过程）。

Answer 1

我目前无法验证，但我相信这会在中起作用

SELECT id, count(id) AS multiplicity 
FROM
(
    SELECT id FROM foo WHERE ...
    UNION ALL
    SELECT id FROM bar WHERE ...
    UNION ALL
    SELECT id FROM baz WHERE ...
) AS TablesTogether
GROUP BY id

Answer 2

这是加权解决方案的格式化版本（weight_Foo和weight_Bar是常数）

SELECT id, sum(fullcount) AS multiplicity
FROM (
    SELECT id, weight_Foo as fullcount FROM Foo WHERE ...
    UNION ALL
    SELECT id, weight_Bar as fullcount FROM Bar WHERE ...
) AS TemporaryTableName
GROUP BY id