SQL：为重复条目创建计数器

I would like to create an integer counter for duplicate entries on an intermediate table in an import step. Let's say I have a table called FinalTable, which looks like this:

我想在导入步骤中为中间表上的重复条目创建一个整数计数器。假设我有一个名为FinalTable的表,它看起来像这样:

FinalTable

ID       Date          SameDateCounter
1003     2012/01/01    NULL
1004     2012/02/01    NULL
1005     2012/03/01    1
1006     2012/03/01    2
1007     2012/03/01    3
1008     2012/04/01    1
1009     2012/04/01    2

And has a unique constraint on (Date, SameDateCounter). In the intermediate step the data is in IntermediateTable, which looks exactly like FinalTable except that it doesn't have any constraints. So the starting point for this problem is here:.

并且(Date,SameDateCounter)具有唯一约束。在中间步骤中,数据位于IntermediateTable中,它看起来与FinalTable完全相同,只是它没有任何约束。所以这个问题的出发点在这里:

IntermediateTable:

ID       Date          SameDateCounter
1003     2012/01/01    NULL
1004     2012/02/01    NULL
1005     2012/03/01    NULL
1006     2012/03/01    NULL
1007     2012/03/01    NULL
1008     2012/04/01    NULL
1009     2012/04/01    NULL

And I need to generate the values for SameDateCounter, starting at one, where the date appears more than once.

我需要为SameDateCounter生成值,从1开始,其中日期出现不止一次。

1 个解决方案

#1

You can use the ROW_NUMBER() function combined with a PARTITION BY clause:

您可以将ROW_NUMBER()函数与PARTITION BY子句结合使用:

;WITH Data AS
(  
   SELECT
       ID, Date,
       SameDateCounter = ROW_NUMBER() OVER (PARTITION BY Date ORDER BY ID DESC)
   FROM dbo.YourTableNameHere
)
SELECT *
FROM Data

The PARTITION BY Date clause groups every separate value of Date into its own "partition", and ROW_NUMBER() hands out numbers starting at 1, for each partition.

PARTITION BY Date子句将Date的每个单独值分组到其自己的“分区”中,并且ROW_NUMBER()为每个分区分配从1开始的数字。

So your SameDayCounter will start at 1 for each new day and will show the rows for that particular day.

因此,您的SameDayCounter将在每个新的一天从1开始,并将显示该特定日期的行。

#1