求PostgreSQL 12中查询物品数≤1的用户的SQL语句

你遇到的错误是因为聚合函数（如count()）不能在WHERE子句中使用，WHERE用于过滤单条行数据，而聚合函数是对分组后的数据集进行计算，需要搭配GROUP BY和HAVING子句来实现分组过滤。另外，原语句用INNER JOIN会漏掉没有任何物品的用户（物品数为0），如果要包含这类用户，应该用LEFT JOIN。

以下是两种可行的写法：

方法一：左连接+分组+HAVING #

SELECT u.id, u.name, u.email, COUNT(i.id) AS item_count
FROM "user" u
LEFT JOIN items i ON u.id = i.user_id
GROUP BY u.id, u.name, u.email
HAVING COUNT(i.id) <= 1;

• LEFT JOIN保证所有用户都被查询到，即使没有关联的物品
• GROUP BY按用户的唯一标识和字段分组，确保每个用户作为一个分组
• HAVING在分组后过滤出物品数≤1的用户

方法二：子查询统计物品数后关联 #

SELECT u.*, COALESCE(item_stats.item_count, 0) AS item_count
FROM "user" u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS item_count
    FROM items
    GROUP BY user_id
) item_stats ON u.id = item_stats.user_id
WHERE COALESCE(item_stats.item_count, 0) <= 1;

• 子查询先统计每个用户的物品数量
• LEFT JOIN关联用户表和统计结果，COALESCE将无物品用户的NULL值转为0
• WHERE直接过滤物品数≤1的用户

内容的提问来源于stack exchange，提问作者damiTobi Hunjeo