SQL Server:为同一年的记录创建增量计数器?

本文介绍了SQL Server:为同一年的记录创建增量计数器?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含以下列的表格:

I have a table with the following columns:

EmployeeID EventDate (mm/dd/yyyy) Event

我需要创建一个新列，该列将按年份计算每个事件并以##-yyyy 格式插入一个值.例如，我想创建一个类似 Counter 的文件:

I need to create a new column which would count each event by year and insert a value in the format ##-yyyy. For example, I want to create a file like Counter:

EmployeeID EventDate Event Counter ------------------------------------------ 001 01/05/2018 A 01-2018 002 12/12/2018 A 01-2018 001 03/01/2019 A 01-2019 001 04/05/2019 A 02-2019 002 05/05/2019 A 01-2018

我不需要按事件或事件类型来计数.我只需要为一年中的每个事件包含一个计数器，并在每个事件发生的日期之前为每个事件增加一个计数器.因此，2019 年 1 月的事件数量将低于 2019 年 6 月的事件数量.

I don't need to count by the event or event type. I just need to include a counter for each event in a year and to increment for each event by the date that it happened. So an event in January 2019 would have a lower number than an event in June 2019.

推荐答案

如果这是 SQL 服务器:

If this was SQL server:

SELECT ..., CONCAT( FORMAT( ROW_NUMBER() OVER(PARTITION BY employeeid, YEAR(eventdate) ORDER BY eventdate ASC), 'D2' ), '-', YEAR(eventdate) ) as counter FROM ...

ROW_NUMBER() 将从 1 增加一个计数器，重新启动每个不同的员工/年.我们将其格式化为前导 0，然后附加一个连字符和年份

ROW_NUMBER() will increment a counter from 1, that restarts every different employee/year. We format this to have a leading 0, then append a hyphen and the year

如果您希望同一月份的所有事件都具有相同的编号，请考虑使用 DENSE_RANK() OVER(PARTITION BY employeeid, YEAR(eventdate) ORDER BY MONTH(eventdate)) 而不是行号.

If you want all events in the same month to have the same number, consider a DENSE_RANK() OVER(PARTITION BY employeeid, YEAR(eventdate) ORDER BY MONTH(eventdate)) instead of a row number.