如何在MySQL中查询带考勤状态的整月日历考勤记录

核心逻辑是先构造全量的员工-日期维度基准集合，再关联打卡数据计算出勤状态，具体实现步骤如下：

• 提取全量唯一工号：从attendance表中通过DISTINCT idnumber获取所有需要统计的员工列表
• 构造全量基准行：将员工列表与calendar_dimension日历表做笛卡尔交叉连接，生成每个员工对应全月每一天的基准数据行
• 关联打卡数据：将基准行与attendance打卡表做左连接，关联条件为工号一致且日历日期等于打卡记录的date_created
• 计算出勤状态：
  • 若日历表的datestatus为Holiday，状态直接标记为Holiday
  • 若打卡表对应记录的punchin和punchout非空，状态标记为Present
  • 非节假日且无打卡记录的日期，状态标记为Absent
• 处理区间展示：不需要逐行展示的2021-11-01到2021-11-25区间，可以通过过滤后单独拼接合并行实现需求中的展示效果。

以下是MySQL语法的参考实现代码：

SELECT 
  e.idnumber,
  -- 可按需替换为公司规定的默认上下班时间
  IFNULL(a.punchin, '7:30:00') AS punchin,
  IFNULL(a.punchout, '16:00:00') AS punchout,
  c.date AS date_created,
  CASE
    WHEN c.datestatus = 'Holiday' THEN 'Holiday'
    WHEN a.idnumber IS NOT NULL THEN 'Present'
    ELSE 'Absent'
  END AS Status
FROM 
  -- 提取所有唯一员工工号
  (SELECT DISTINCT idnumber FROM attendance) e
CROSS JOIN 
  calendar_dimension c
LEFT JOIN 
  attendance a 
  ON e.idnumber = a.idnumber 
  AND c.date = a.date_created
-- 过滤掉不需要逐行展示的1-25号区间，可单独用UNION拼接合并行
WHERE c.date >= '2021-11-26'
ORDER BY e.idnumber, c.date

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