oracle给表中重复数据增加顺序号

表结构及测试数据如下:

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;

测试数据输出结果:

sekect * from t_row_str;

ID COL

1 a

1 b

1 c

2 a

2 d

2 e

3 c

执行如下SQL语句获得想要的结果:

-- row_number() over(partition by #{重复的列名} order by #{排序的列名})
select id, col, row_number() over(partition by id order by col) as rowno from t_row_str;

输出结果:

ID COL ROWNO

1 a 1

1 b 2

1 c 3

2 a 1

2 d 2

2 e 3

3 c 1