PostgreSQL中LEFT JOIN无法按预期显示0计数的问题

问题根源分析 #

你当前的写法存在两个核心问题：

1. 语法错误与条件位置错误：原SQL中B.CrimeSubType = b.CrimeSubType是笔误（逻辑错误且大小写不统一），正确关联条件应为a.CrimeSubType = b.CrimeSubType；同时将a.year=2024放在ON子句中，会导致LEFT JOIN时保留不符合年份的A表数据，无法精准过滤2024年的记录。
2. 缺失全量维度组合：普通LEFT JOIN只能保留左表的现有行，但你的左表（A或B）无法覆盖「所有社区+所有犯罪分类」的全量组合——比如Jeff Park没有2024年的VIOLENT CRIME记录，这个组合在原始表中不存在，自然无法通过常规关联生成。

解决方案：构造全量维度组合再左连接统计数据 #

要生成包含0值的完整结果，必须先构造所有社区和所有唯一犯罪分类的笛卡尔积（即每个社区对应所有分类），再左连接2024年的犯罪统计数据，最后将NULL计数转为0。

正确SQL实现 #

WITH all_dimensions AS (
    -- 生成所有社区与所有犯罪分类的全量配对
    SELECT DISTINCT a.Neighborhood, b.Category
    FROM A
    CROSS JOIN (SELECT DISTINCT Category FROM B) AS b
),
yearly_crime_stats AS (
    -- 统计2024年各社区各分类的实际犯罪次数
    SELECT
        a.Neighborhood,
        b.Category,
        COUNT(*) AS actual_count
    FROM A
    INNER JOIN B
        ON a.CrimeType = b.CrimeType
        AND a.CrimeSubType = b.CrimeSubType
    WHERE a.Year = 2024
    GROUP BY a.Neighborhood, b.Category
)
-- 全量维度左连接统计数据，补全0值
SELECT
    ad.Neighborhood,
    ad.Category,
    COALESCE(ycs.actual_count, 0) AS Counts
FROM all_dimensions ad
LEFT JOIN yearly_crime_stats ycs
    ON ad.Neighborhood = ycs.Neighborhood
    AND ad.Category = ycs.Category
ORDER BY ad.Category, ad.Neighborhood;

代码解释 #

1. all_dimensions CTE：通过CROSS JOIN将A表中所有唯一社区与B表中所有唯一犯罪分类进行配对，确保每个社区的每个分类都有一条基础记录。
2. yearly_crime_stats CTE：先过滤A表2024年的数据，再关联B表获取分类名称，统计各社区各分类的实际犯罪次数。
3. 最终查询：将全量维度表与统计结果左连接，用COALESCE函数把未匹配到的统计值（NULL）转换为0，得到包含所有组合的完整统计结果。

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