如何实现每7行递增一次计数的SQL查询？

我参考过以下类似场景的解决方案：

• 每10条记录将结果字段递增1
• 每3行生成递增序列
• 每150行递增整数
• 另一种每3行生成递增序列的实现

我需要实现每7行递增一次计数，尝试了上述思路编写代码，但未得到预期结果：

select pk,
    row_number() over (order by pk) as r,
    (row_number() over (order by pk) -1) /7 as r2,
    (row_number() over (order by pk) -1) /7 +1 as r3,
    (row_number() over (order by pk) -1) %7 +1 as r4,
    (row_number() over (order by pk) -1) %7 as r5,
    (row_number() over (order by pk) ) %7 +1 as r6
from (
    select 1 as pk
    union
    select 2 as pk
    union 
    select 3 as pk
    union
    select 4 as pk
    union
    select 5 as pk
    union 
    select 6 as pk
    union
    select 7 as pk
    union
    select 8 as pk
    union 
    select 9 as pk
) a;

预期结果： #

正确解法 #

你编写的代码中，r3字段的计算逻辑完全符合需求，简化后的可用代码如下：

select pk,
    (row_number() over (order by pk) - 1) / 7 + 1 as r
from (
    select 1 as pk union select 2 as pk union select 3 as pk
    union select 4 as pk union select 5 as pk union select 6 as pk
    union select 7 as pk union select 8 as pk union select 9 as pk
) a;

该逻辑通过row_number()获取行号，先减1让起始行号从0开始，除以7取整（整数除法）后再加1，即可实现每7行递增一次计数的效果。

内容的提问来源于stack exchange，提问作者StackOne