oracle 代码行,Oracle生成数据

这可能是一种方式:

select floor((level -1) / 4) +1 as groupNR,

row_number() over (partition by floor((level -1) / 4) +1 order by level) as itemNR

from dual

connect by level <= 25;

甚至,如果没有解析函数:

level - 4*floor((level -1) / 4) as itemNR

测试:

SQL> select floor((level -1) / 4) +1 as groupNR,

2 row_number() over (partition by floor((level -1) / 4) +1 order by level) as itemNR,

3 level - 4*floor((level -1) / 4) as itemNR_2

4 from dual

5 connect by level <= 25

6 order by level;

GROUPNR ITEMNR ITEMNR_2

---------- ---------- ----------

1 1 1

1 2 2

1 3 3

1 4 4

2 1 1

2 2 2

2 3 3

2 4 4

3 1 1

3 2 2

3 3 3

3 4 4

4 1 1

4 2 2

4 3 3

4 4 4

5 1 1

5 2 2

5 3 3

5 4 4

6 1 1

6 2 2

6 3 3

6 4 4

7 1 1

25 rows selected.