sql - PostgreSQL:加入另一个表的计数

Question

我有三个表:users、items 和 boards。

create table users (
    id serial primary key,
    name text
);  

create table items (
    id serial primary key,
    user_id int,
    title text
);  

create table boards (
    id serial primary key,
    user_id int,
    title text
);n

我想选择所有用户以及每个用户拥有的项目和板的数量:

select
    users.*,
    count(items) as item_count,
    count(boards) as board_count
from users
    left join items on items.user_id = users.id
    left join boards on boards.user_id = users.id
group by users.id;

不幸的是，这并没有给我正确的计数。计数比应有的高得多。发生了什么，为什么，我该如何解决？

Answer 1

问题是由两个左连接产生重复值引起的。

要解决这个问题，您可以只计算不同的值

SELECT u.id, u.name,
       COUNT(DISTINCT i.id) item_count,
       COUNT(DISTINCT b.id) board_count
  FROM users u LEFT JOIN items i
    ON u.id = i.user_id LEFT JOIN boards b
    ON u.id = b.user_id
 GROUP BY u.id, u.name

或者单独计算每个用户的项目和板，然后将它们与用户合并

SELECT u.*, 
       COALESCE(i.item_count, 0) item_count, 
       COALESCE(b.board_count, 0) board_count
  FROM users u LEFT JOIN
(
  SELECT user_id, COUNT(*) item_count
    FROM items
   GROUP BY user_id
) i ON u.id = i.user_id LEFT JOIN
(
  SELECT user_id, COUNT(*) board_count
    FROM boards
   GROUP BY user_id
) b ON u.id = b.user_id

这是 SQLFiddle 演示