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
??
?
?
?
?
