问题描述
我想在 SQL 中创建一个数据查询，以递增地编号行组，在公共日期时间分组，并在下一个日期时间保持组号"递增，依此类推.正如我在使用 partition by 语句时所看到的那样，这些组号"不得为每个组重置.这是我的示例数据:
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 分区的唯一 ROW 编号.我真正需要的是，对于每个 ts_DateTime 分区，RowFilter 列应该看起来像所需的结果列.
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
查询，
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 演示
这篇关于如何在SQL中返回每个组的增量组号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！