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

??

?

?

?

?