SQL 在 Postgres SELECT 语句中选择另一个表中的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4535782/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select count of rows in another table in a Postgres SELECT statement
提问by Ass3mbler
I don't know quite how to phrase this so please help me with the title as well. :)
我不知道如何表达这个，所以请帮助我的标题。:)
I have two tables. Let's call them Aand B. The Btable has a a_idforeign key that points at A.id. Now I would like to write a SELECTstatement that fetches all Arecords, with an additional column containing the count of Brecords per Arow for each row in the result set.
我有两张桌子。让我们称他们为A和B。该B表有一个a_id指向的外键A.id。现在我想编写一个SELECT获取所有A记录的语句，其中包含一个附加列，其中包含结果集中每一行的B每A行记录数。
I'm using Postgresql 9 right now, but I guess this would be a generic SQL question?
我现在正在使用 Postgresql 9，但我想这将是一个通用的 SQL 问题？
EDIT:
编辑：
In the end I went for trigger-cache solution, where A.b_countis updated via a function each time Bchanges.
最后我选择了触发器缓存解决方案，A.b_count每次B更改时都会通过函数更新。
回答by Ass3mbler
SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A
回答by Gerry Shaw
I think the comment by @intgr in another answer is so valuable I'm putting forward this as an alternate answer as this method allows you to filter the calculated column efficiently.
我认为@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
回答by intgr
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
If you're using Django ORM you can simply write:
如果您使用的是 Django ORM，您可以简单地编写：
res = A.objects.annotate(Count('b'))
print res[0].b__count # holds the result count
回答by Eldar Agalarov
Accepted answer is inefficient (slow) based on my tests. The subquery of table B executing for every row of table A. I'm using following approach based on grouping and joining. It works much faster:
根据我的测试，接受的答案效率低下（缓慢）。表 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
Another variant:
另一种变体：
SELECT A.id, COUNT(B.a_id) AS quantity FROM A
LEFT JOIN B ON B.a_id = A.id
GROUP BY A.id
回答by Eldar Agalarov
To answer my own question:
回答我自己的问题：
SELECT a.id, a.other_column, ...,
(SELECT COUNT(*) FROM b where b.a_id = a.id) AS b_count
FROM a;
