从多个表中获取外键计数

click 发表于 Dev

点击

我有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获取的，并对出现的次数进行计数。但是，如果密钥不在B或C中出现，则它不会显示在输出中（例如，输出中id = 2）。如何从表A和联接/联合B和C开始选择以获得所需的输出

欧文·布兰德斯特

如果查询涉及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()。

或a_id来自两个表的UNION ALL ，进行汇总，然后进行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()也仍然不会丢失任何行。在这种情况下，您可能会偶尔得到的NULL值bc_ct。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。