每 3 行生成行号 [英] Generate Row Number for every 3 rows

问题描述

我想为每三行生成一个数字

I want generate number for every three rows

CREATE TABLE #test(period INT)

INSERT INTO #test
VALUES      (602),(603),(604),(605),(606),(607),(608),(609)

我知道我们可以使用 row_number 窗口函数或 while 循环或 cursor

I know we can generate sequence using row_number window function or while loop or cursor

SELECT period,
       ( Row_number()OVER(ORDER BY period) - 1 ) / 3 + 1
FROM   #test 

结果；

+--------+-----+
| period | seq |
+--------+-----+
|    602 |   1 |
|    603 |   1 |
|    604 |   1 |
|    605 |   2 |
|    606 |   2 |
|    607 |   2 |
|    608 |   3 |
|    609 |   3 |
+--------+-----+

是否有其他方法可以数学实现这一目标.期间不会有任何间隔

Is there any other way to achieve this mathematically. There will not be any gaps between the periods

推荐答案

数学或算术方法可能是使用句号本身:

A mathematical or arithmetic approach could be to use the period numbers themselves:

-- table init here
DECLARE @MIN_PERIOD INT = (SELECT MIN(period) FROM #test)

SELECT period,
       (period - @MIN_PERIOD) / 3 + 1 AS seq
FROM   #test 

只要期间之间不会有任何间隔"仍然成立，此方法就有效.

This works as long as "there will not be any gaps between the periods" remains true.

如果您需要在主查询中使用 WHERE 子句，也可以将其应用于 SELECT MIN() 查询.只要 WHERE 不会导致周期间隔，就可以工作.

If you need a WHERE clause on the main query, also apply it to the SELECT MIN() query. Will work as long as the WHERE does not cause period gaps.

这篇关于每 3 行生成行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！