如何用Outer Join关联日期表与业务表聚合生成人员年度记录

你的问题出在原查询的关联逻辑上：直接用all_years左连person时，2023年没有匹配的person记录，导致p.person为null，分组后会生成person为null的行，而非你期望的A。要生成所有人员-年份的组合并正确统计，需要先构造完整的人员-年份笛卡尔积，再关联数据统计。

修改后的SQL如下：

with person as 
(
    select 2021 as cov_year, 'A' as person 
    union all
    select 2021, 'A' 
    union all
    select 2022, 'A' 
    union all
    select 2024, 'A' 
    union all
    select 2024, 'A' 
    union all
    select 2024, 'A'
),
all_years as 
(
    select 2021 as year 
    union all
    select 2022 as year 
    union all
    select 2023 as year 
    union all   
    select 2024 as year
),
distinct_person as (
    -- 提取所有唯一人员，适配多人员场景
    select distinct person from person
)
select 
    y.year, dp.person, 
    -- 统计匹配的记录数，无数据时返回0；若要null可改用case语句
    count(p.cov_year) as ct 
from 
    all_years y 
cross join 
    distinct_person dp -- 生成所有人员-年份的组合
left join 
    person p on y.year = p.cov_year and dp.person = p.person -- 同时匹配年份和人员
group by 
    y.year, dp.person
order by 
    y.year;

关键说明： #

1. 构造完整组合：通过distinct_person获取所有唯一人员，再与all_years做cross join，确保每个人员的每一年都有一条基础记录。
2. 正确关联统计：左连原person表时同时匹配年份和人员，避免无关关联。
3. 计数逻辑：用count(p.cov_year)而非count(*)或count(p.person)，因为count会忽略null值，无匹配数据时自动返回0；如果需要返回null，可以替换为：

   case when count(p.cov_year) = 0 then null else count(p.cov_year) end as ct
   

执行后会得到你期望的输出：

year    person  ct
------------------
2021    A       2
2022    A       1
2023    A       0
2024    A       3

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