PostgreSQL多表关联查询：筛选持有指定车辆的用户并按地区分组统计

原SQL问题原因 #

1. 聚合逻辑错误：直接按region分组时，SUM统计的是整个地区所有符合条件的车辆关联记录总数，没有先对单个用户做是否同时持有两辆车的判断，结果完全不符合需求。
2. 关联逻辑缺陷：使用INNER JOIN会过滤掉没有关联到目标车辆的人员和地区，导致Europe这类没有符合条件用户的地区不会出现在结果中，无法输出0的统计值。
3. 字段引用错误：cars表的品牌字段为make，原SQL误写为name。

修正后SQL #

SELECT
    t.region,
    SUM(t.has_both) AS own_honda_and_nissan
FROM (
    -- 内层先判断每个用户是否同时持有Honda和Nissan
    SELECT 
        p.id,
        p.region,
        CASE WHEN COUNT(DISTINCT c.make) = 2 THEN 1 ELSE 0 END AS has_both
    FROM people p
    LEFT JOIN people_cars pc ON p.id = pc.person_id
    LEFT JOIN cars c ON pc.car_id = c.id AND c.make IN ('Honda', 'Nissan')
    GROUP BY p.id, p.region
) t
GROUP BY t.region
ORDER BY own_honda_and_nissan DESC;

逻辑说明 #

• 内层子查询先按用户维度聚合，通过COUNT(DISTINCT c.make)判断单个用户关联的目标品牌数量是否为2，兼容同一个用户持有多辆同品牌车的场景。
• 关联cars表时直接过滤目标品牌，减少无效计算。
• 外层按region聚合，对每个用户的达标标记求和即可得到对应地区符合条件的总人数，自动处理无达标用户时返回0的需求。
• 使用LEFT JOIN保留所有人员和地区，不会遗漏无达标用户的地区。

内容的提问来源于stack exchange，提问作者Jon Lamb