在 PostgresSELECT 语句中选择另一个表中的行计数

我不知道该怎么说，所以请你也帮我把标题写出来。 :)

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

I'm using Postgresql 9 right now, but I guess this would be a generic SQL question?

EDIT:

最后，我选择了触发器缓存解决方案，每次 B发生变化时，通过一个函数更新 A.b_count。

103956 次浏览
小开

回答我自己的问题:

SELECT a.id, a.other_column, ...,
(SELECT COUNT(*) FROM b where b.a_id = a.id) AS b_count
FROM a;
小开
最佳答案 
SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A
小开

The subquery solution given above is inefficient. The trigger solution is probably best in a mostly-read database, but for the record here's a join approach that will perform better than a subquery:

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
小开

我认为@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
小开

根据我的测试，接受的答案是低效(缓慢)的。对表 A 的每一行执行表 B 的子查询。我使用以下基于分组和连接的方法。它的工作速度快得多:

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
小开

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

I have a Table A of "Approvers" 我有一个“审批任务”表 B

我想显示一个所有审批人员的列表，以及他们拥有多少活动审批任务的计数。现在，我的 SQL 知识有限，但无论我尝试不同类型的连接，我的审批人列表是不完整的。为什么？我需要在表 B 上设置一个过滤器，以便只返回活动任务。如果审批人只有不活动/完成的任务，则不计数。这应该显示0，但是由于某种原因它根本不显示行。

因此，我使用了一个子查询，它完美地工作。


提交答案