如何查找工人未到岗日期？基于calendar表与entry考勤表的实现方法

核心思路 #

不需要逐个遍历工人ID排查，先生成所有工人和2021年所有日期的全量打卡对照组合，再和实际打卡记录做差集匹配，即可批量得到所有工人的未到岗日期，执行效率远高于逐ID查询。

通用兼容方案（支持所有SQL语法环境） #

通过笛卡尔积生成全量对照组合，左连接过滤未匹配到的打卡记录即为缺勤：

SELECT
  w.id AS worker_id,
  c.date AS absent_date
FROM
  -- 提取所有需要统计的工人唯一ID
  (SELECT DISTINCT id FROM entry) w
-- 拼接每个工人对应的2021年所有日期
CROSS JOIN calendar c
-- 左连接匹配实际打卡记录
LEFT JOIN entry e 
  ON w.id = e.id 
  AND c.date = e.date
-- 过滤未匹配到打卡记录的日期（即缺勤）
WHERE e.date IS NULL
-- 可选按工人ID、日期排序方便查看
ORDER BY w.id, c.date;

优化调整项 #

• 如果calendar表存储了多年日期，可提前过滤2021年数据：CROSS JOIN (SELECT date FROM calendar WHERE YEAR(date) = 2021) c
• 如果只需要统计部分工人，在提取工人ID的子句中加过滤条件即可：(SELECT DISTINCT id FROM entry WHERE id IN (1,2,3)) w
• 大数据量场景下，给entry表的id和date字段加联合索引，可大幅提升查询速度。

简化语法方案（适配支持EXCEPT的数据库，如PostgreSQL、SQL Server） #

直接取全量对照组合和实际打卡记录的差集，写法更简洁：

SELECT w.id, c.date
FROM (SELECT DISTINCT id FROM entry) w
CROSS JOIN calendar c
EXCEPT
SELECT id, date FROM entry
ORDER BY id, date;

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