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