如何用SQL按日期为每个员工生成递增的4位格式计数

需要列出每个EmployeeId在各日期下的每一条记录，每行显示一个递增的4位数字计数（如0001、0002）。规则是：特定EmployeeId在特定日期每出现一次，计数就加1。例如EmployeeId 143在2023-01-18出现两次，第一行显示0001，第二行显示0002。

当前使用的SQL语句 #

SELECT 
    FORMAT(COUNT(e.EmployeeId), '0000') AS [Count] 
    , e.EmployeeId
    , c.CheckDate
FROM dbo.Check c
JOIN dbo.Employees e
    ON e.EmployeeId = c.CreatedBy
GROUP BY c.CheckDate, e.EmployeeId
ORDER BY c.CheckDate DESC;

当前查询结果 #

期望查询结果 #

解决方案 #

不能用GROUP BY聚合总数，需要用窗口函数ROW_NUMBER()按日期和员工ID分组生成递增计数。修改后的SQL如下：

SELECT 
    FORMAT(ROW_NUMBER() OVER (PARTITION BY c.CheckDate, e.EmployeeId ORDER BY c.CheckId), '0000') AS [Count]
    , e.EmployeeId
    , c.CheckDate
FROM dbo.Check c
JOIN dbo.Employees e
    ON e.EmployeeId = c.CreatedBy
ORDER BY c.CheckDate DESC, e.EmployeeId, [Count];

关键说明 #

• PARTITION BY c.CheckDate, e.EmployeeId：将数据按日期和员工ID分成独立的组，每个组内单独计算递增计数
• ORDER BY c.CheckId：指定组内记录的排序依据，建议使用Check表的主键（如CheckId）保证计数顺序稳定；如果没有主键，可用其他能唯一标识每条记录的字段（比如记录创建时间）
• FORMAT(..., '0000')：将生成的数字计数格式化为4位字符串，不足位数自动补零

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