下面把student表添加省份的字段,然后使用partition 按省份内学生成绩排名
alter table student add province nvarchar(20); go update top (6) student set province='JiangSu'; go update student set province='ShangHai' where province is null; go select * from student;
select ROW_NUMBER() over (partition by province order by score desc) as 排名, name, score, province from student
上图就是结果,JiangSu的6个人,排名就就到6;二ShangHai的7个人排名就到7。
当然Row_number()还可以替换成rank(),dense_rank()。