如何编写SQL查询展示员工月度考勤及缺勤（含NULL值）

你的需求核心是要展示每个员工当月每一天的考勤状态，包括缺勤日期（此时checkin和checkout为NULL）。原来的查询有两个关键问题：一是仅从已有签到记录中提取日期，会漏掉全月无任何员工签到的日期；二是没有实现“每个员工对应全月每一天”的关联逻辑，导致无法完整展示单个员工的全月考勤。

下面是完善后的完整查询（以SQL Server为例，适配你的表结构）：

WITH DateRange AS (
    -- 生成指定月份的所有日期（这里以2019年10月为例）
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, '2019-10-01'), 0) AS [Date]
    UNION ALL
    -- 递归生成该月剩余的所有日期
    SELECT DATEADD(DAY, 1, [Date])
    FROM DateRange
    WHERE DATEADD(DAY, 1, [Date]) < DATEADD(MONTH, DATEDIFF(MONTH, 0, '2019-10-01') + 1, 0)
),
EmployeeDates AS (
    -- 关联全月日期和所有员工，得到每个员工的每一天记录
    SELECT 
        dr.[Date],
        e.Emp_ID,
        e.Name AS Employee_Name
    FROM DateRange dr
    CROSS JOIN Emp e
)
SELECT 
    ed.[Date],
    ed.Employee_Name,
    ci.checkin,
    ci.checkout
FROM EmployeeDates ed
-- 左连接考勤表，匹配员工ID和日期
LEFT JOIN Checkinout ci 
    ON ed.Emp_ID = ci.Emp_ID_Fk 
    AND CONVERT(DATE, ci.checkin) = ed.[Date]
ORDER BY ed.Employee_Name, ed.[Date];

关键部分解释： #

• DateRange CTE：通过递归方式生成指定月份的完整日期范围，不再依赖已有签到记录，确保全月每一天都能被覆盖。你可以把'2019-10-01'换成动态参数（比如当前月份的第一天）来适配不同月份的查询。
• EmployeeDates CTE：用CROSS JOIN将全月日期与员工表做笛卡尔积，这是实现缺勤展示的核心——不管员工有没有签到，这一天的基础记录都会存在。
• 主查询的LEFT JOIN：通过员工ID+日期的双重匹配关联考勤表，若员工当天无签到记录（Checkinout中无对应行），checkin和checkout会自动显示为NULL，完美符合你要的缺勤展示效果。

额外优化建议： #

如果存在同一员工同一天多次打卡的情况，可以用聚合函数提取最早签到和最晚签退时间：

SELECT 
    ed.[Date],
    ed.Employee_Name,
    MIN(ci.checkin) AS checkin,  -- 最早签到时间
    MAX(ci.checkout) AS checkout -- 最晚签退时间
FROM EmployeeDates ed
LEFT JOIN Checkinout ci 
    ON ed.Emp_ID = ci.Emp_ID_Fk 
    AND CONVERT(DATE, ci.checkin) = ed.[Date]
GROUP BY ed.[Date], ed.Employee_Name
ORDER BY ed.Employee_Name, ed.[Date];

如果需要动态查询当前月份，可修改DateRange的起始日期：

WITH DateRange AS (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM DateRange
    WHERE DATEADD(DAY, 1, [Date]) < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
),
-- 后续部分同上

内容的提问来源于stack exchange，提问作者D. Jhon