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 rnFROM 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
页:
[1]