如何关联两个CTE并通过GROUP BY生成正确的汇总结果？

背景说明 #

两个CTE派生自同一张表：

• open_bridges：筛选条件为Structure Code IN ('F')
• gikl_all_strucs：筛选条件为Structure Code IN ('G','I','K','L')

open_bridges的前24行样本数据：

单独查询的正确结果 #

查询1：统计各结构类型下建造年份≤对应年份的数量 #

SELECT 
      oba.Structure_type,
     SUM(CASE WHEN oba.YEAR_BUILT<='2016' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2016,
     SUM(CASE WHEN oba.YEAR_BUILT<='2017' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2017,
     SUM(CASE WHEN oba.YEAR_BUILT<='2018' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2018,
     SUM(CASE WHEN oba.YEAR_BUILT<='2019' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2019,
     SUM(CASE WHEN oba.YEAR_BUILT<='2020' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2020,
     SUM(CASE WHEN oba.YEAR_BUILT<='2021' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2021,
     SUM(CASE WHEN oba.YEAR_BUILT<='2022' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2022,
     SUM(CASE WHEN oba.YEAR_BUILT<='2023' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2023
    FROM open_bridges oba 
    GROUP BY oba.structure_type
    ORDER BY oba.structure_type DESC

查询结果：

查询2：统计各结构类型下状态授予日期年份≥2016的数量 #

SELECT 
            gika.Structure_type,
            SUM(CASE WHEN EXTRACT(YEAR FROM gika.status_granted_date) >='2016' THEN 1 ELSE 0 END) AS gik2016
FROM gikl_all_strucs gika
GROUP BY gika.structure_type

查询结果：

关联查询的问题 #

尝试通过LEFT JOIN关联两个CTE并GROUP BY时，gik2016列全为0，使用的SQL语句如下：

SELECT 
     oba.STructure_type,
     SUM(CASE WHEN oba.YEAR_BUILT<='2016' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2016,
     SUM(CASE WHEN EXTRACT(YEAR FROM gika.current_status_granted_date) >='2016' THEN 1 ELSE 0 END) AS gik2016,            
     SUM(CASE WHEN oba.YEAR_BUILT<='2017' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2017,
     SUM(CASE WHEN oba.YEAR_BUILT<='2018' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2018,
     SUM(CASE WHEN oba.YEAR_BUILT<='2019' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2019,
     SUM(CASE WHEN oba.YEAR_BUILT<='2020' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2020,
     SUM(CASE WHEN oba.YEAR_BUILT<='2021' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2021,
     SUM(CASE WHEN oba.YEAR_BUILT<='2022' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2022,
     SUM(CASE WHEN oba.YEAR_BUILT<='2023' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2023
FROM open_bridges oba LEFT JOIN gikl_all_strucs gika 
                ON oba.structure_type=gika.structure_type 
                        AND oba.structure_no=gika.structure_no 
                        AND oba.year_built=gika.year_built 
                        AND oba.structure_status_type_code=gika.structure_status_type_code
    GROUP BY oba.structure_type
    ORDER BY oba.structure_type DESC

预期汇总表：

修正方案 #

问题原因 #

1. 关联条件过于严格：两个CTE的Structure Code互斥（一个为F，一个为G/I/K/L），通过structure_no、year_built等字段关联不会有匹配数据，导致gika表字段全为NULL，SUM计算结果为0。
2. 字段名错误：查询2使用status_granted_date，但关联查询中误写为current_status_granted_date，字段不匹配。

修正后的SQL #

WITH open_bridges_stats AS (
    SELECT 
        oba.Structure_type,
        SUM(CASE WHEN oba.YEAR_BUILT<='2016' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2016,
        SUM(CASE WHEN oba.YEAR_BUILT<='2017' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2017,
        SUM(CASE WHEN oba.YEAR_BUILT<='2018' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2018,
        SUM(CASE WHEN oba.YEAR_BUILT<='2019' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2019,
        SUM(CASE WHEN oba.YEAR_BUILT<='2020' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2020,
        SUM(CASE WHEN oba.YEAR_BUILT<='2021' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2021,
        SUM(CASE WHEN oba.YEAR_BUILT<='2022' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2022,
        SUM(CASE WHEN oba.YEAR_BUILT<='2023' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2023
    FROM open_bridges oba 
    GROUP BY oba.structure_type
),
gikl_stats AS (
    SELECT 
        gika.Structure_type,
        SUM(CASE WHEN EXTRACT(YEAR FROM gika.status_granted_date) >=2016 THEN 1 ELSE 0 END) AS gik2016
    FROM gikl_all_strucs gika
    GROUP BY gika.structure_type
)
SELECT 
    os.Structure_type,
    os.y2016,
    COALESCE(gs.gik2016, 0) AS gik2016,
    os.y2017,
    os.y2018,
    os.y2019,
    os.y2020,
    os.y2021,
    os.y2022,
    os.y2023
FROM open_bridges_stats os
LEFT JOIN gikl_stats gs ON os.Structure_type = gs.Structure_type
ORDER BY os.Structure_type DESC;

修正要点 #

• 先分别对两个CTE按structure_type完成统计，得到各自的汇总结果
• 仅通过structure_type关联两个汇总表，避免原始数据无匹配的问题
• 使用COALESCE处理NULL值，确保无匹配时显示0
• 修正字段名错误，将current_status_granted_date改回status_granted_date

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