如何在SQL中为每个组返回一个增量组编号 [英] How to return a incremental group number per group in SQL

问题描述

我希望在SQL中创建一个数据查询，以增加行数组，在一个通用的日期时间上分组，并在下一个日期时间继续增加组数等等。正如我在使用分区声明时所看到的，这些组号不得重置。以下是我的示例数据：

I would like create a data query in SQL to incrementally number groups of rows, grouped on a common datetime and keep the "group numbers" incrementing on the next datetime and so on. These "group numbers" must not reset for each group as I have seen when using the partition by statement. Here is my sample data:

ts_DateTime          |ID   |Value|RowFilter|RequiredResult
--------------------------
2013/01/09 09:23:16  |8009 |0    |1        |1
2013/01/09 09:23:16  |8010 |0    |2        |1
2013/01/09 09:23:16  |8026 |0    |3        |1

2013/01/09 09:23:22  |8026 |0    |1        |2

2013/01/09 09:23:28  |8009 |0    |1        |3
2013/01/09 09:23:28  |8010 |0    |2        |3
2013/01/09 09:23:28  |8026 |0    |3        |3

2013/01/09 09:27:03  |8009 |0    |1        |4
2013/01/09 09:27:03  |8010 |0    |2        |4
2013/01/09 09:27:03  |8026 |0    |3        |4

2013/01/09 09:27:09  |8009 |0    |1        |5
2013/01/09 09:27:09  |8010 |0    |2        |5
2013/01/09 09:27:09  |8026 |0    |3        |5

2013/01/09 09:27:15  |8009 |0    |1        |6
2013/01/09 09:27:15  |8010 |0    |2        |6
2013/01/09 09:27:15  |8026 |0    |3        |6

我用来获得这些结果的查询是：

The query I am using to get these results is :

select hl.ts_DateTime,  hl.Tagname as [ID],  hl.TagValue as [Value],
ROW_NUMBER() OVER (PARTITION BY hl.ts_datetime ORDER BY hl.tagname) AS RowFilter
from Table1 hl

所以基本上，看着RowFilter列，我得到每个 ts_DateTime 分区的唯一行号。我真正需要的是，对于每个 ts_DateTime 分区，RowFilter列应该看起来像Required result列。

So basically, looking at the RowFilter column, I am getting a unique ROW number per ts_DateTime partition. What I actually need is that for each ts_DateTime partition the RowFilter column should look like the Required result column.

推荐答案

您不应该使用 ROW_NUMBER（），

• 使用 DENSE_RANK（）代替


• 删除 PARTITION BY

• use DENSE_RANK() instead
• remove PARTITION BY

查询，

query,

SELECT hl.ts_DateTime,  
       hl.Tagname as [ID],  
       hl.TagValue as [Value],
       DENSE_RANK() OVER (ORDER BY ts_datetime) AS RowFilter
FROM   Table1 hl 
ORDER  BY RowFilter

• SQLFiddle演示


• SQLFiddle Demo

这篇关于如何在SQL中为每个组返回一个增量组编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！