语法格式:row_number() over(partition by 分组列 order by 排序列 desc)
一个很简单的例子
1,先做好准备
create table test1(
id varchar(4) not null,
name varchar(10) null,
age varchar(10) null
);
select * from test1;
insert into test1(id,name,age) values(1,'a',10);
insert into test1(id,name,age) values(1,'a2',11);
insert into test1(id,name,age) values(2,'b',12);
insert into test1(id,name,age) values(2,'b2',13);
insert into test1(id,name,age) values(3,'c',14);
insert into test1(id,name,age) values(3,'c2',15);
insert into test1(id,name,age) values(4,'d',16);
insert into test1(id,name,age) values(5,'d2',17);
select t.id,
t.name,
t.age,
row_number() over(partition by t.id order by t.age asc) rn
from test1 t
结果:
id name age rn
1 a 10 1
1 a2 11 2
2 b 12 1