如何编写SQL查询找出仅打卡上班未打卡下班的员工？

我有一张打卡记录表，记录员工当日的上班、下班打卡情况。要求打卡上班的员工必须打卡下班，但存在员工只打上班卡忘打下班卡的情况。现在需要编写查询语句，找出所有未打卡下班的员工及其最后一条打卡记录，且不想维护排班表，仅基于现有打卡记录校验（缺勤/休假员工不会打上班卡）。

示例数据 #

DECLARE @PunchCards TABLE
(
    [Name] VARCHAR(20),
    [Shift] VARCHAR(30)
);
--John & Kate have clocked in & out properly, Doug forgot to clock out
INSERT INTO @PunchCards
    (Name, Shift)
VALUES
    ('John',           -- Name - varchar(20)
     'Start Day Shift' -- Shift - varchar(30)
    ),
    ('Doug',           -- Name - varchar(20)
     'Start Day Shift' -- Shift - varchar(30)
    ),
    ('John',         -- Name - varchar(20)
     'End Day Shift' -- Shift - varchar(30)
    ),
    ('Kate',             -- Name - varchar(20)
     'Start Night Shift' -- Shift - varchar(30)
    ),
    ('Kate',           -- Name - varchar(20)
     'End Night Shift' -- Shift - varchar(30)
    )

基于上述数据，查询应返回一行：Name = Doug，Shift = Start Day Shift。

解决思路与实现代码 #

思路1：统计班次打卡次数差 #

通过分组统计每个员工的上班打卡（Start开头）和对应下班打卡（End开头）的次数，筛选出上班次数多于下班次数的员工，同时获取其最后一条上班打卡记录。

WITH PunchSummary AS (
    SELECT 
        Name,
        -- 统计上班打卡次数
        SUM(CASE WHEN Shift LIKE 'Start%' THEN 1 ELSE 0 END) AS StartCount,
        -- 统计下班打卡次数
        SUM(CASE WHEN Shift LIKE 'End%' THEN 1 ELSE 0 END) AS EndCount,
        -- 获取该员工最后一条上班打卡记录
        MAX(CASE WHEN Shift LIKE 'Start%' THEN Shift END) AS LastStartShift
    FROM @PunchCards
    GROUP BY Name
)
SELECT Name, LastStartShift AS Shift
FROM PunchSummary
WHERE StartCount > EndCount;

思路2：精准匹配对应班次类型 #

提取班次的核心类型（如Day Shift、Night Shift），按员工+班次类型分组，统计每组的上下班打卡次数，筛选出未完成下班打卡的记录。

WITH EmployeeShifts AS (
    SELECT 
        Name,
        Shift,
        -- 提取班次核心类型（去掉Start/End前缀）
        RIGHT(Shift, LEN(Shift) - CHARINDEX(' ', Shift)) AS ShiftType,
        -- 标记打卡类型：上班/下班
        CASE WHEN LEFT(Shift, 5) = 'Start' THEN 'In' ELSE 'Out' END AS PunchType
    FROM @PunchCards
),
ShiftPairs AS (
    SELECT 
        Name,
        ShiftType,
        COUNT(CASE WHEN PunchType = 'In' THEN 1 END) AS InCount,
        COUNT(CASE WHEN PunchType = 'Out' THEN 1 END) AS OutCount,
        MAX(CASE WHEN PunchType = 'In' THEN Shift END) AS LastInShift
    FROM EmployeeShifts
    GROUP BY Name, ShiftType
)
SELECT Name, LastInShift AS Shift
FROM ShiftPairs
WHERE InCount > OutCount;

思路3：结合窗口函数（适用于含打卡时间的场景） #

如果表中包含打卡时间字段（如PunchTime），可以通过窗口函数获取每个员工的最后一条打卡记录，再检查是否存在对应的下班打卡：

-- 假设表结构新增PunchTime DATETIME字段
WITH OrderedPunches AS (
    SELECT 
        Name,
        Shift,
        -- 按打卡时间倒序，标记每个员工的最新打卡记录
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY PunchTime DESC) AS RN,
        -- 标记是否为上班打卡
        CASE WHEN Shift LIKE 'Start%' THEN 1 ELSE 0 END IsStart
    FROM PunchCards
),
EmployeeLastPunch AS (
    SELECT Name, Shift, IsStart
    FROM OrderedPunches
    WHERE RN = 1
),
ShiftCheck AS (
    SELECT 
        elp.Name,
        elp.Shift,
        -- 检查是否存在对应的下班打卡记录
        EXISTS (
            SELECT 1 
            FROM PunchCards pc
            WHERE pc.Name = elp.Name
              AND pc.Shift = REPLACE(elp.Shift, 'Start', 'End')
        ) HasEndPunch
    FROM EmployeeLastPunch elp
)
SELECT Name, Shift
FROM ShiftCheck
WHERE IsStart = 1 AND HasEndPunch = 0;

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