如何连接多个表进行计数？

Question

我有三个表，名为houses，trees，rivers。所有这些表都有city_id列。我想按城市对总数进行分组。城市在另一个表中。我的数据库是postgresql。

city_name    trees    houses      rivers
City-1       1000     200         1
City-2       300      100         2
City-3       4000     210         4

我可以拿到树

SELECT 
    city.name as city_name, 
    count(*) as trees
FROM trees as t, cities as city
WHERE t.city_id = city.city_id
GROUP BY city.name

但是我不能在sama查询中连接三个表。

Answer 1

为了避免在JOIN中重复行的问题，最简单的方法可能是在子查询中进行聚合，然后对它们进行JOIN：

SELECT c.name, 
       COALESCE(t.cnt, 0) AS trees,
       COALESCE(h.cnt, 0) AS houses,
       COALESCE(r.cnt, 0) AS rivers
FROM cities c
LEFT JOIN (SELECT city_id, COUNT(*) AS cnt
           FROM trees
           GROUP BY city_id) t ON t.city_id = c.city_id
LEFT JOIN (SELECT city_id, COUNT(*) AS cnt
           FROM houses
           GROUP BY city_id) h ON h.city_id = c.city_id
LEFT JOIN (SELECT city_id, COUNT(*) AS cnt
           FROM rivers
           GROUP BY city_id) r ON r.city_id = c.city_id

如果给定的城市没有树木、房屋或河流，我们就使用LEFT JOIN。

Demo on dbfiddle

Answer 2

尼克答案的另一种选择：

SELECT 
    city.name as city_name, 
    count(distinct t.id) as trees,
    count(distinct h.id) as houses,
    count(distinct r.id) as rivers
FROM  cities as city
left join trees as t on t.city_id = city.city_id
left join rivers as r on r.city_id = city.city_id
left join houses as h on h.city_id = city.city_id
GROUP BY city.name

--

不确定Postgres的具体性能影响，但这里有一篇文章(相当古老，所以事情可能已经发生了变化)表明Postgres中的count(distinct)可能会很慢，以及一些选项：

postgresql COUNT(DISTINCT ...) very slow