如何在MySQL中用ROW_NUMBER统计员工每日打卡并区分上下班状态

问题根源 #

现有查询的窗口函数PARTITION BY分组规则存在问题：datediff(PunchTime, '2021-08-11')=0只会将2021-08-11当天的打卡记录归为同一组，其余所有日期的记录都会被归到同一个大组，最终导致行号跨日累加。

修改方案 #

将ROW_NUMBER窗口函数的PARTITION BY分组条件调整为EmpID+打卡日期（精确到天），即可实现每个员工、每一天的打卡记录独立生成排序序号。

修改后代码（MySQL/PostgreSQL通用版） #

WITH PunchDataPlus AS (
SELECT 
    pd.*, 
    ei.Name, 
    ROW_NUMBER() OVER(PARTITION BY EmpID, DATE(PunchTime) ORDER BY PunchTime) AS RN 
FROM PunchData pd 
LEFT JOIN EmployeeInfo ei ON pd.EmpID = ei.EmpID
), FinalPunchData AS (
SELECT 
    PunchDataPlus.*, 
    CASE WHEN RN % 2 = 1 THEN 'IN' ELSE 'OUT' END AS inOutCol 
FROM PunchDataPlus
) 
SELECT * FROM FinalPunchData WHERE EmpID = 4 ORDER BY PunchTime DESC;

其他数据库适配调整 #

只需要替换提取打卡日期的函数即可：

• SQL Server：将DATE(PunchTime)替换为CAST(PunchTime AS DATE)
• Oracle：将DATE(PunchTime)替换为TRUNC(PunchTime)

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