按日期为每个员工创建一系列计数

Question

我试图列出employeeid在每个日期以行分隔并以0000格式出现的次数。每当特定的employeeid在特定的日期出现时，计数就会增加。所以EmployeeId 143在2023-01-18出现了两次，所以第一行是0001，第二行是0002

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;

我现在得到的:

tbody> <<tr>

COUNT	EmployeeId	CheckDate
0002	143	2023-01-18 00:00:00.000
0002	143	2023-01-17 00:00:00.000
0002	427	2023-01-17 00:00:00.000
0007	607	2023-01-17 00:00:00.000

我认为你的问题是，当你真正需要一个窗口函数时，你正在聚合。

确保您的夫妇的唯一值<CheckDate,>如果有多个副本，可以使用ROW_NUMBER窗口函数。

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