求MySQL/SQL Server查询语句：按日期展示员工上下班考勤记录

原始考勤表结构与数据 #

查询需求 #

需要查询指定日期范围（示例：2024-01-01 至 2024-01-03）内的考勤记录，要求：

• 展示日期范围内的所有日期
• 每个员工每日的Clock_In（上班时间）和Clock_Out（下班时间）
• 无对应记录时显示NULL

期望输出格式 #

实现语句 #

MySQL 查询语句 #

-- 生成日期范围表（递归方式支持任意长度的日期区间）
WITH RECURSIVE date_range AS (
    SELECT '2024-01-01' AS Date_Log
    UNION ALL
    SELECT DATE_ADD(Date_Log, INTERVAL 1 DAY)
    FROM date_range
    WHERE Date_Log < '2024-01-03'
),
-- 获取所有员工ID
employee_list AS (
    SELECT DISTINCT Card_ID FROM attendance_table
)
-- 交叉连接生成所有日期-员工组合，左连接考勤数据并聚合
SELECT
    dr.Date_Log,
    el.Card_ID,
    MAX(CASE WHEN at.Status_Entry = 'IN' THEN at.Date_Entry END) AS Clock_In,
    MAX(CASE WHEN at.Status_Entry = 'OUT' THEN at.Date_Entry END) AS Clock_Out
FROM date_range dr
CROSS JOIN employee_list el
LEFT JOIN attendance_table at
    ON el.Card_ID = at.Card_ID
    AND DATE(at.Date_Entry) = dr.Date_Log
GROUP BY dr.Date_Log, el.Card_ID
ORDER BY dr.Date_Log, el.Card_ID;

SQL Server 查询语句 #

-- 生成日期范围表（递归方式支持任意长度的日期区间）
WITH date_range AS (
    SELECT CAST('2024-01-01' AS DATE) AS Date_Log
    UNION ALL
    SELECT DATEADD(DAY, 1, Date_Log)
    FROM date_range
    WHERE Date_Log < '2024-01-03'
),
-- 获取所有员工ID
employee_list AS (
    SELECT DISTINCT Card_ID FROM attendance_table
)
-- 交叉连接生成所有日期-员工组合，左连接考勤数据并聚合
SELECT
    dr.Date_Log,
    el.Card_ID,
    MAX(CASE WHEN at.Status_Entry = 'IN' THEN at.Date_Entry END) AS Clock_In,
    MAX(CASE WHEN at.Status_Entry = 'OUT' THEN at.Date_Entry END) AS Clock_Out
FROM date_range dr
CROSS JOIN employee_list el
LEFT JOIN attendance_table at
    ON el.Card_ID = at.Card_ID
    AND CAST(at.Date_Entry AS DATE) = dr.Date_Log
GROUP BY dr.Date_Log, el.Card_ID
ORDER BY dr.Date_Log, el.Card_ID;

注意事项 #

• 替换语句中的attendance_table为实际的考勤表名称
• 若仅需短日期范围，也可替换递归CTE为手动枚举的方式（如使用UNION ALL或VALUES生成日期序列）

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