oracle为相同行记录增加序号列(row_number()函数)
文章提取自 : http://blog.sina.com.cn/s/blog_475839a50100s2q3.html
表结构及测试数据如下:
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