PostgreSQL多表JOIN时如何按两个条件统计物品使用次数？

原查询错误原因 #

你第一次的查询结果出错是因为两次左连接产生了笛卡尔积：

• 对于id为1的物品，第一次左连接item_usages（别名total_usage）后会匹配到4条符合条件的记录
• 第二次左连接item_usages（别名user_usage）时，会将上一步的4条记录和本次匹配到的3条user_id=99的记录做关联，最终生成4*3=12条中间记录
• 直接对这12条记录做count统计，自然会得到12的错误结果

更高性能的查询方案 #

你后续写出的子查询版本虽然结果正确，但需要对item_usages表做两次扫描（一次子查询聚合，一次关联统计用户使用次数），还会生成临时中间表，数据量较大时性能表现一般。

推荐使用条件聚合实现需求，仅需一次左连接即可完成统计，执行效率更高：

兼容标准SQL的通用写法 #

select 
    items.*,
    count(item_usages.id) as total_usage,
    count(case when item_usages.user_id = 99 then 1 end) as user_usage
from items
left join item_usages on items.id = item_usages.item_id
group by items.id;

PostgreSQL专属简洁写法 #

PostgreSQL支持FILTER语法专门用于条件聚合，写法更清晰易读：

select 
    items.*,
    count(item_usages.id) as total_usage,
    count(*) filter (where item_usages.user_id = 99) as user_usage
from items
left join item_usages on items.id = item_usages.item_id
group by items.id;

该方案仅对item_usages表扫描一次，无需生成临时中间表，如果item_usages的item_id字段建有索引，查询性能会进一步提升。

内容的提问来源于stack exchange，提问作者Muhammad Dyas Yaskur