sql - 从多个表中获取外键的计数

Question

我有 3 个表，表 B 和 C 通过外键引用表 A。我想在 PostgreSQL 中编写一个查询，以获取 A 的所有 ID 以及它们在 B 和 C 中的总出现次数。

   a      |     b      |     c
-----------------------------------    
id | txt  |  id | a_id |  id | a_id  
---+----  |  ---+----- |  ---+------ 
1  |  a   |  1  |  1   |  1  |  3    
2  |  b   |  2  |  1   |  2  |  4    
3  |  c   |  3  |  3   |  3  |  4    
4  |  d   |  4  |  4   |  4  |  4    

所需的输出(只是 A 中的 ID 和 B 和 C 中的总计数):

id | Count
---+-------  
1  |  2      -- twice in B
2  |  0      -- occurs nowhere
3  |  2      -- once in B & once in C
4  |  4      -- once in B & thrice in C

到目前为止的 SQL SQL Fiddle :

SELECT a_id, COUNT(a_id)
FROM
( SELECT a_id FROM b
  UNION ALL 
  SELECT a_id FROM c
) AS union_table
GROUP BY a_id

我编写的查询从 B 和 C 获取并计算出现次数。但是如果 key 没有出现在 B 或 C 中，它就不会出现在输出中(例如输出中的 id=2)。我如何从表 A 开始我的选择并加入/联合 B 和 C 以获得所需的输出

Answer 1

如果查询涉及到 b 和/或 c 的大部分，那么先聚合再加入会更高效。
我希望这两个变体要快得多:

SELECT a.id,
     , COALESCE(b.ct, 0) + COALESCE(c.ct, 0) AS bc_ct
FROM   a
LEFT   JOIN (SELECT a_id, count(*) AS ct FROM b GROUP BY 1) b USING (a_id)
LEFT   JOIN (SELECT a_id, count(*) AS ct FROM c GROUP BY 1) c USING (a_id);

您需要考虑某些 a_id 根本不存在于 a 和/或 b 中的可能性。 count() 永远不会返回 NULL，但面对 LEFT JOIN 时，这是一种冰冷的安慰，它让您得到 NULL 尽管如此，缺失行的值。您必须准备NULL。使用 COALESCE() .

或者 UNION ALL a_id 来自两个表，聚合，然后 JOIN:

SELECT a.id
     , COALESCE(ct.bc_ct, 0) AS bc_ct
FROM   a
LEFT   JOIN (
   SELECT a_id, count(*) AS bc_ct
   FROM (
      SELECT a_id FROM b
      UNION ALL
      SELECT a_id FROM c
      ) bc
   GROUP  BY 1
   ) ct USING (a_id);

可能比较慢。但仍然比目前提出的解决方案更快。您可以不用 COALESCE() 并且仍然不会丢失任何行。在这种情况下，您可能偶尔会得到 bc_ct 的 NULL 值。