oracle 获取行数序号,oracle为相同行记录增加序号列(row_number()函数)

表结构及测试数据如下:

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语句获得想要的结果:

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