SQL查询员工各考勤段上下班时间及打卡次数实现方案

修改方案 #

你目前的SQL已经通过rn1 - rn2正确划分了同一个员工的连续考勤段，只需要在最终查询的SELECT子句中新增COUNT(*)统计每个分组的总行数即可，对应就是当前考勤段的打卡总次数。同时为了匹配你给出的预期结果格式，补充了结果表的自增序号id字段。

修改后完整SQL #

WITH cte AS 
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY DateP, TimeP) rn1,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY DateP, TimeP) rn2
    FROM 
        EmployeeTable
),
cte2 AS 
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY EmployeeID, rn1 - rn2
                           ORDER BY DateP, TimeP) rn_first,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID, rn1 - rn2
                           ORDER BY DateP DESC, TimeP DESC) rn_last
    FROM 
        cte
)
SELECT
    ROW_NUMBER() OVER (ORDER BY MIN(DateP), MIN(TimeP)) AS id,
    EmployeeID,
    MAX(CASE WHEN rn_first = 1 THEN DateP END) AS MinDateP,
    MAX(CASE WHEN rn_first = 1 THEN TimeP END) AS TimeMinDateP,
    MAX(CASE WHEN rn_last = 1  THEN DateP END) AS MaxDateP,
    MAX(CASE WHEN rn_last = 1  THEN TimeP END ) AS TimeMaxDateP,
    COUNT(*) AS Count -- 新增的考勤段打卡次数统计字段
FROM 
    cte2
GROUP BY
    EmployeeID,
    rn1 - rn2
ORDER BY
    MIN(DateP), MIN(TimeP);

如果不需要预期结果里的自增id列，删掉SELECT子句中对应的ROW_NUMBER() AS id行即可。

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