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