如何在SQL中为每位员工的每日记录添加自动递增序号列

原始数据 #

PK_Log_ID   FK_Emp_ID   LogTime                  Direction
13          3           2022-08-10 09:25:54.563  0
14          3           2022-08-10 13:25:54.563  1
15          3           2022-08-11 09:25:54.563  0
16          3           2022-08-11 11:25:54.563  1
17          3           2022-08-11 12:25:54.563  0
18          3           2022-08-11 13:25:54.563  1
19          3           2022-08-11 14:25:54.563  0
20          3           2022-08-11 18:25:54.563  1
21          4           2022-08-07 09:25:54.563  0
22          4           2022-08-07 13:25:54.563  1
23          4           2022-08-07 14:25:54.563  0
24          4           2022-08-07 18:25:54.563  1
25          4           2022-08-08 09:25:54.563  0
26          4           2022-08-08 13:25:54.563  1

期望结果 #

新增Rowmunber列，对每位员工的每日记录按时间顺序递增编号，每组从1开始：

PK_Log_ID   FK_Emp_ID   LogTime                  Direction   Rowmunber
13          3           2022-08-10 09:25:54.563  0           1
14          3           2022-08-10 13:25:54.563  1           2
15          3           2022-08-11 09:25:54.563  0           1
16          3           2022-08-11 11:25:54.563  1           2
17          3           2022-08-11 12:25:54.563  0           3
18          3           2022-08-11 13:25:54.563  1           4
19          3           2022-08-11 14:25:54.563  0           5
20          3           2022-08-11 18:25:54.563  1           6
21          4           2022-08-07 09:25:54.563  0           1
22          4           2022-08-07 13:25:54.563  1           2
23          4           2022-08-07 14:25:54.563  0           3
24          4           2022-08-07 18:25:54.563  1           4
25          4           2022-08-08 09:25:54.563  0           1
26          4           2022-08-08 13:25:54.563  1           2
27          4           2022-08-08 14:25:54.563  0           3

解决方案 #

使用SQL的ROW_NUMBER()窗口函数，按员工ID和日志日期分区，再按日志时间排序生成序号：

SELECT 
    PK_Log_ID,
    FK_Emp_ID,
    LogTime,
    Direction,
    ROW_NUMBER() OVER (
        PARTITION BY FK_Emp_ID, CAST(LogTime AS DATE) 
        ORDER BY LogTime
    ) AS Rowmunber
FROM 你的表名;

说明 #

• PARTITION BY FK_Emp_ID, CAST(LogTime AS DATE)：将数据按员工ID和日志日期分成独立分组，每个分组单独生成序号
• ORDER BY LogTime：每个分组内按日志时间升序排列，确保序号按时间顺序生成
• ROW_NUMBER()：为每个分组内的行分配从1开始的连续递增序号

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