T-SQL 行号在 N 后重新启动 [英] T-SQL Row Number Restart after N

问题描述

如何在一定数量的行后重新启动我的 Row_Number.

How can I restart my Row_Number after a certain number of rows.

例如

怎么转

ID  RowNumber
-------------
100 1
101 2
102 3
125 4
126 5
148 6
149 7
150 8
151 9

进入

ID  RowNumber
-------------
100 1
101 2
102 3
125 4
126 1
148 2
149 3
150 4
151 1

注意没有办法根据ID进行分区，实际上是随机的.

Note that there is no way to partition based on the ID, that is actually random.

我尝试使用递归 CTE 并且非常接近.

I have tried to use a recursive CTE and got very close.

这个:

DECLARE @MyTable TABLE (ID INT, RowNumber INT)

INSERT INTO @MyTable (ID,RowNumber)
    SELECT 100,1 UNION ALL
    SELECT 101,2 UNION ALL
    SELECT 102,3 UNION ALL
    SELECT 125,4 UNION ALL
    SELECT 126,5 UNION ALL
    SELECT 148,6 UNION ALL
    SELECT 149,7 UNION ALL
    SELECT 150,8 UNION ALL
    SELECT 151,9;

WITH CTE AS 
(
    SELECT  ID,
            RowNumber ,
            1 AS Steps
    FROM @MyTable   
    WHERE ID = 100

    UNION ALL
    SELECT M.ID ,
           M.RowNumber +1,
           CASE WHEN C.Steps = 4 THEN 0 ELSE C.Steps END +1 AS steps
    FROM @MyTable M
        INNER JOIN CTE C ON C.RowNumber = M.RowNumber

)
SELECT * 
FROM CTE

返回:

ID  RowNumber
-------------
100 1   1
100 2   2
101 3   3
102 4   4
125 5   1
126 6   2
148 7   3
149 8   4
150 9   1
151 10  2

离第一个ID很近.

任何帮助都会很棒.

谢谢，

会

推荐答案

使用模运算符:

select id, rownumber, 1 + ((rownumber - 1) % 4)
from table t;

如果rownumber不是列，也可以使用row_number()函数:

If rownumber is not a column, you can also use the row_number() function:

select id, row_number() over (order by id),
       1 + ((row_number() over (order by id) - 1) % 4)
from table t;

这篇关于T-SQL 行号在 N 后重新启动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！