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 A
and B
. The B
table has a a_id
foreign key that points at A.id
. Now I would like to write a SELECT
statement that fetches all A
records, with an additional column containing the count of B
records per A
row for each row in the result set.
I'm using Postgresql 9 right now, but I guess this would be a generic SQL question?
EDIT:
In the end I went for trigger-cache solution, where A.b_count
is updated via a function e开发者_运维技巧ach time B
changes.
SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A
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.
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
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:
res = A.objects.annotate(Count('b'))
print res[0].b__count # holds the result count
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:
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
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;
Whilst a sub-query may be less efficient, how much less efficient depends on the use-case. Another thing to consider is the filters that are being used.
I have a Table A of "Approvers" I have a Table B of "Approval tasks"
I want to show a list of ALL approvers along with a count of how many ACTIVE approval tasks they have. Now, my knowledge of SQL is limited, but no matter what I tried with the different types of join, my list of approvers was incomplete. Why? I need to have a filter on table B so that only active tasks are returned. If an approver only has inactive/complete tasks, there is no count. This should show 0, but for some reason it just doesn't show the row at all.
So, I use a sub-query and it works perfectly.
精彩评论