在 Postgres SELECT 语句中选择另一个表中的行数

Question

我不太知道如何表达这个，所以也请帮我写一下标题。 :)

我有两张桌子。我们将它们称为 A 和 B。 B 表有一个指向 A.id 的 a_id 外键。现在我想编写一个 SELECT 语句来获取所有 A 记录，并附加一个列，其中包含每个 B 记录的计数结果集中的每一行都有一个 行。

我现在正在使用 Postgresql 9，但我想这将是一个通用的 SQL 问题？

编辑：

最后我选择了触发器缓存解决方案，其中每次 B 更改时都会通过函数更新 A.b_count 。

Answer 1

SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A

Answer 2

我认为 @intgr 在另一个答案中的评论非常有价值，我将其作为替代答案提出，因为此方法允许您有效地过滤计算列。

SELECT
  a.*,
  COUNT(b.id) AS b_count

FROM a
INNER JOIN b on b.a_id = a.id
WHERE a.id > 50 AND b.ID < 100 -- example of filtering joined tables, optional

GROUP BY a.id
HAVING COUNT(b.id) > 10 -- example of filtering calculated column, optional
ORDER BY a.id

Answer 3

上面给出的子查询解决方案效率很低。触发器解决方案可能在大多数读取的数据库中是最好的，但为了记录，这里有一种连接方法，其性能比子查询更好：

SELECT a.id, a.xxx, count(*)
FROM a JOIN b ON (b.a_id = a.id)
GROUP BY a.id, a.xxx

如果您使用 Django ORM，您可以简单地编写：

res = A.objects.annotate(Count('b'))
print res[0].b__count  # holds the result count

Answer 4

根据我的测试，接受的答案效率低下（慢）。表 B 的子查询对表 A 的每一行执行。我使用以下基于分组和联接的方法。它的工作速度更快：

SELECT A.id, QTY.quantity FROM A
LEFT JOIN
    (SELECT COUNT(B.a_id) AS quantity, B.a_id FROM B GROUP BY B.a_id) AS QTY
ON A.id = QTY.a_id

另一种变体：

SELECT A.id, COUNT(B.a_id) AS quantity FROM A
LEFT JOIN B ON B.a_id = A.id
GROUP BY A.id

Answer 5

回答我自己的问题：

SELECT a.id, a.other_column, ..., 
(SELECT COUNT(*) FROM b where b.a_id = a.id) AS b_count
FROM a;

Answer 6

虽然子查询的效率可能较低，但效率降低的程度取决于用例。另一件需要考虑的事情是正在使用的过滤器。

我有一个“批准者”表 A
我有一个“审批任务”表 B，

我想显示所有审批者的列表以及他们拥有的活动审批任务的计数。现在，我对 SQL 的了解有限，但无论我尝试使用不同类型的联接，我的批准者列表都是不完整的。为什么？我需要在表 B 上有一个过滤器，以便只返回活动任务。如果审批者只有非活动/已完成的任务，则没有计数。这应该显示 0，但由于某种原因它根本不显示该行。

所以，我使用了子查询，它工作得很好。