SQL实现按日期标记人员存在状态（1存在/0不存在）的查询需求

现有数据表 #

Table1 #

Table2 #

需求 #

编写SELECT查询语句，按PersonalNr分组，对指定日期（2023-09-01、2023-09-02）标记是否存在记录：有记录则显示1，无则显示0，预期结果如下：

尝试的错误SQL #

SELECT t1.personalnr,
  case when EXISTS (  select* from table2 
    where date between '2023-01-09' and '2023-02-09' and t1.personalnr = t2.personalnr) then '1' else '0'
  end
case when EXISTS (  select* from table2 
    where date between '2023-02-09' and '2023-03-09' and t1.personalnr = t2.personalnr) then '1' else '0'
  end
FROM [BKLatWORK].[dbo].[Table1] t1
left join table2 t2 on t2.PersonalNr = t1.PersonalNr
group by t1.PersonalNr, t2.personalnr

错误原因分析 #

1. 日期范围完全错误：需求是9月的日期，代码中写的是1月、2月的范围，完全不匹配目标日期
2. 子查询别名未定义：exists子查询中引用t2但未给table2设置别名，会导致语法错误
3. case语句缺少列名：两个case结果没有指定列名，无法对应预期的日期列
4. 分组逻辑错误：group by中包含t2.personalnr，left join后t2的该字段可能为null，会导致分组重复或错误

正确解决方案 #

使用条件聚合（MAX结合CASE）实现，按PersonalNr分组后，对每个目标日期判断是否存在记录：

SELECT 
    t1.PersonalNr,
    MAX(CASE WHEN t2.Date = '2023-09-01' THEN 1 ELSE 0 END) AS [2023-09-01],
    MAX(CASE WHEN t2.Date = '2023-09-02' THEN 1 ELSE 0 END) AS [2023-09-02]
FROM [BKLatWORK].[dbo].[Table1] t1
LEFT JOIN Table2 t2 ON t1.PersonalNr = t2.Personalnr
GROUP BY t1.PersonalNr

方案说明 #

• 用LEFT JOIN保证Table1中所有PersonalNr都被保留
• CASE语句判断当前行是否匹配目标日期，匹配返回1否则0
• MAX聚合函数：同一PersonalNr下只要有一行匹配日期，就会保留1（因为1>0），实现“存在即标记1”的逻辑
• 给每个CASE结果指定列名，对应预期的日期列

如果需要支持动态日期（而非固定写死），可以使用PIVOT语法，但固定日期场景下条件聚合更简单直观。

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