oracle给每行加上序号,oracle为相同行记录增加序号列(row_number()函数)

[sql]代码库表结构及测试数据如下:

Sql代码

CREATE TABLE t_row_str(

ID INT,

col VARCHAR2(10));

INSERT INTO t_row_str VALUES(1,'a');

INSERT INTO t_row_str VALUES(1,'b');

INSERT INTO t_row_str VALUES(1,'c');

INSERT INTO t_row_str VALUES(2,'a');

INSERT INTO t_row_str VALUES(2,'d');

INSERT INTO t_row_str VALUES(2,'e');

INSERT INTO t_row_str VALUES(3,'c');

COMMIT;

SELECT * FROM t_row_str;

测试数据输出结果:

结果集代码

ID COL

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

1 a

1 b

1 c

2 a

2 d

2 e

3 c

执行如下SQL语句获得想要的结果:

Sql代码

SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) AS rn

FROM t_row_str

输出结果:

结果集代码

ID COL RN

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

1 a 1

1 b 2

1 c 3

2 a 1

2 d 2

2 e 3

3 c 1