SQL 如何在SQL中返回每个组的增量组号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14359749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return a incremental group number per group in SQL
提问by DLR
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:
我想在 SQL 中创建一个数据查询，以递增编号的行组，在公共日期时间分组，并保持“组号”在下一个日期时间递增，依此类推。正如我在使用 partition by 语句时所看到的那样，这些“组号”不得为每个组重置。这是我的示例数据：
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
So basically, looking at the RowFilter column, I am getting a unique ROW number per ts_DateTimepartition. What I actually need is that for each ts_DateTimepartition the RowFilter column should look like the Required result column.
所以基本上，查看 RowFilter 列，我得到每个ts_DateTime分区的唯一 ROW 编号。我真正需要的是，对于每个ts_DateTime分区，RowFilter 列应该看起来像所需的结果列。
回答by John Woo
you shouldn't be using ROW_NUMBER(),
你不应该使用ROW_NUMBER()，
- use
DENSE_RANK()instead - remove
PARTITION BY
- 使用
DENSE_RANK()替代 - 消除
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
回答by Micha? Turczyn
I think you are looking for this:
我想你正在寻找这个：
ROW_NUMBER() OVER (PARTITION BY hl.id ORDER BY hl.ts_DateTime) AS RowFilter
