SQL查询给定日期范围内学生表存在但考勤表无匹配记录的学生

核心思路 #

先生成指定查询范围内的所有日期序列，再把该序列和全量学生表做笛卡尔积，得到「所有学生+所有查询日期」的全量基准集合，最后用该基准集合左关联学生考勤表，即可自动补全无打卡记录的学生行，缺失的考勤字段会自动赋值为NULL。

通用SQL实现（支持MySQL 8.0+、PostgreSQL等主流数据库） #

WITH RECURSIVE date_range AS (
    -- 替换为你需要的查询起始日期
    SELECT '2024-01-01' AS query_date
    UNION ALL
    SELECT DATE_ADD(query_date, INTERVAL 1 DAY)
    FROM date_range
    -- 替换为你需要的查询结束日期
    WHERE query_date < '2024-01-31'
)
SELECT 
    s.`Student#`,
    s.FirstName,
    s.LastName,
    sa.`Date`,
    sa.TimeIn,
    sa.TimeOut
FROM date_range dr
-- 学生表和日期序列做笛卡尔积，生成每个学生对应每一天的基准行
CROSS JOIN Student s
-- 左关联考勤表，按学生ID和日期匹配打卡记录
LEFT JOIN `Student Attendance` sa 
    ON s.`Student#` = sa.`Student#` 
    AND dr.query_date = sa.`Date`
ORDER BY s.`Student#`, dr.query_date;

注意事项 #

• 表名、字段名包含空格、#等特殊符号，需要用反引号包裹避免语法报错
• 低版本不支持递归CTE的数据库，可以提前维护一张日期维度表，替换上述SQL中的date_range部分即可
• 若需要单独筛选缺勤记录，直接在WHERE条件中加sa.Student# IS NULL即可过滤

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