sql - 对多个表进行 SELECT 查询

Question

我有两个表，foo 和 bar:

+----+-----+    +----+-----+
| id | val |    | id | val |
+----+-----+    +----+-----+
|  1 | qwe |    |  1 | asd |
|  2 | rty |    |  3 | fgh |
+----+-----+    +----+-----+

id 在这里不是唯一的。并非 foo 中的所有 ID 在 bar 中都有对应的 ID，反之亦然。我需要计算两个表中具有特定 ID 的所有行，并将它们呈现在一个新表中，例如:

+----+-----------+-----------+
| id | count_foo | count_bar |
+----+-----------+-----------+
|  1 |         1 |         1 |
|  2 |         1 |         0 |
|  3 |         0 |         1 |
+----+-----------+-----------+

我试过UNION SELECT:

SELECT id, COUNT(id) AS count_foo, 0 AS count_bar FROM foo GROUP BY id
UNION SELECT id, 0, COUNT(id) FROM bar GROUP BY id;

但是这会输出 id=1 的行两次，比如

+----+-----------+-----------+
| id | count_foo | count_bar |
+----+-----------+-----------+
|  1 |         1 |         0 |  <- not good
|  2 |         1 |         0 |
|  1 |         0 |         1 |  <- not good
|  3 |         0 |         1 |
+----+-----------+-----------+

我也尝试过LEFT JOIN:

SELECT id, COUNT(foo.id) AS count_foo, COUNT(bar.id) AS count_bar
FROM foo LEFT JOIN bar USING(id) GROUP BY id;

但此查询会忽略表 bar 中 ID 在表 foo 中缺失的行:

+----+-----------+-----------+
| id | count_foo | count_bar |
+----+-----------+-----------+
|  1 |         1 |         1 |
|  2 |         1 |         0 |
+----+-----------+-----------+  <- can I haz `id=3`?

我错过了什么？什么是正确的查询或阅读正确的手册？

谢谢。

Answer 1

您可能想尝试以下方法:

SELECT d.id,
       (SELECT COUNT(*) FROM foo WHERE id = d.id) count_foo,
       (SELECT COUNT(*) FROM bar WHERE id = d.id) count_bar
FROM   ((SELECT id FROM foo) UNION (SELECT id FROM bar)) d;

测试用例:

CREATE TABLE foo (id int, val varchar(5));
CREATE TABLE bar (id int, val varchar(5));

INSERT INTO foo VALUES (1, 'qwe');
INSERT INTO foo VALUES (2, 'rty');
INSERT INTO bar VALUES (1, 'asf');
INSERT INTO bar VALUES (3, 'ghj');

结果:

+------+-----------+-----------+
| id   | count_foo | count_bar |
+------+-----------+-----------+
|    1 |         1 |         1 |
|    2 |         1 |         0 |
|    3 |         0 |         1 |
+------+-----------+-----------+
3 rows in set (0.00 sec)