SQL日期范围查询分组名称显示NULL，需修正匹配预期结果

原始数据表 #

原查询语句 #

with scheduler as (
  select convert(datetime, '2024-11-05 00:00:00', 120) as 'dt' -- end date
  union all
  select  dateadd(DAY, -1, dt) from scheduler 
  where dateadd(dd, -1, dt) >= convert(datetime, '2024-11-01 00:00:00', 120) -- start date
)
select convert(date, a.dt) as 'date', b.group_name, b.employee 
from scheduler a
left join tbl_a b
order by a.dt, b.group_name;

当前查询结果 #

期望结果 #

修正后的查询语句 #

with scheduler as (
  select convert(datetime, '2024-11-05 00:00:00', 120) as 'dt' -- end date
  union all
  select dateadd(DAY, -1, dt) from scheduler 
  where dateadd(dd, -1, dt) >= convert(datetime, '2024-11-01 00:00:00', 120) -- start date
),
groups as (
  select distinct group_name from tbl_a -- 获取所有唯一分组
)
select 
  convert(date, s.dt) as 'date',
  g.group_name,
  t.employee
from scheduler s
cross join groups g -- 生成日期与分组的全组合
left join tbl_a t 
  on convert(date, s.dt) = t.日期 
  and g.group_name = t.group_name -- 按日期+分组匹配原表数据
order by s.dt, g.group_name;

关键调整说明 #

1. 新增groups公共表表达式，提取所有唯一分组，确保不会遗漏任何分组信息
2. 使用cross join生成日期范围与所有分组的笛卡尔积，保证每个日期下每个分组都有一行记录
3. 左连接原表时同时匹配日期和分组字段，精准关联对应数据，无匹配数据时员工字段自动显示NULL

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