如何对比Master与Log表，找出特定工作周/年度缺失出勤记录的员工？

表结构与数据 #

Master表 #

empid|Name
-----+-----
 1   | XYZ
 2   | abz
 3   | yzz

Log表 #

empid|name|workweek|year|attendance
-----+----+--------+----+-----------
 1   | XYZ|  1     |2023|Present
 2   | abz|  1     |2023|Present
 2   | abz|  2     |2023|Present
 3   | yzz|  2     |2023|Present

需求 #

输出包含所有员工在每个已存在的workweek和year组合下的记录，Log表中缺失的员工记录需标记为Absent，期望输出如下：

empid|name|workweek|year|attendance
 -----+----+--------+----+-------------
   1  | XYZ|  1     |2023|Present
   2  | abz|  1     |2023|Present
   3  | yzz|  1     |2023|Absent
   2  | abz|  2     |2023|Present
   3  | yzz|  2     |2023|Present
   1  | XYZ|  2     |2023|Absent

用户尝试的查询 #

select *
from log l
left join Master M on l.empid = m.empid

解决思路与示例代码 #

核心思路 #

要得到所有员工对应所有工作周的记录，需要先构建所有员工与所有唯一(workweek, year)组合的完整集合，再与Log表关联补全考勤状态：

1. 提取Log表中所有不重复的workweek和year组合；
2. 将该组合集合与Master表做交叉连接，生成所有应存在的员工-工作周记录；
3. 左连接Log表，匹配员工ID、工作周、年份；
4. 用COALESCE函数将缺失的考勤状态替换为Absent。

完整SQL示例 #

-- 生成所有员工与所有工作周的组合，再关联考勤记录
SELECT
    m.empid,
    m.Name AS name,
    w.workweek,
    w.year,
    COALESCE(l.attendance, 'Absent') AS attendance
FROM
    Master m
CROSS JOIN
    -- 获取所有唯一的工作周+年份组合
    (SELECT DISTINCT workweek, year FROM Log) w
LEFT JOIN
    Log l ON m.empid = l.empid AND w.workweek = l.workweek AND w.year = l.year
ORDER BY
    w.year, w.workweek, m.empid;

代码说明 #

• (SELECT DISTINCT workweek, year FROM Log)：提取Log中所有已存在的工作周和年份组合，避免重复生成无效记录；
• CROSS JOIN：将每个员工与每个工作周组合配对，得到完整的记录框架；
• LEFT JOIN Log：关联已有考勤数据，未匹配到的记录中attendance会为NULL；
• COALESCE(l.attendance, 'Absent')：把NULL值替换为Absent，符合需求；
• ORDER BY：按年份、工作周、员工ID排序，与期望输出格式一致。

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