SQL 分组排序的窗口函数 rank, dense_rank, row_number

建表

create table score 
( 
	name varchar(20), 
	subject varchar(20), 
	score int 
);
-- 2.插入测试数据 
insert into score(name,subject,score) values('张三','语文',98);
insert into score(name,subject,score) values('张三','数学',80);
insert into score(name,subject,score) values('张三','英语',90);
insert into score(name,subject,score) values('李四','语文',88);
insert into score(name,subject,score) values('李四','数学',86);
insert into score(name,subject,score) values('李四','英语',88);
insert into score(name,subject,score) values('李明','语文',60);
insert into score(name,subject,score) values('李明','数学',86);
insert into score(name,subject,score) values('李明','英语',88);
insert into score(name,subject,score) values('林风','语文',74);
insert into score(name,subject,score) values('林风','数学',99);
insert into score(name,subject,score) values('林风','英语',59);
insert into score(name,subject,score) values('严明','英语',96);

rank

select 
  subject, 
  name, 
  score, 
  rank() over (partition by subject order by score) as rank 
from score;

dense_rank

select 
  subject, 
  name, 
  score, 
  dense_rank() over (partition by subject order by score) as rank 
from score;

row_number

select 
  subject, 
  name, 
  score, 
  row_number() over (partition by subject order by score) as rank 
from score;

使用场景：
如果需要找出 topN 的学生，允许第N名并列，则用 rank()
如果需要找出 topN 的分数和对应的学生，允许结果远大于N人，则用 dense_rank()
如果只要找出N个人，多一个也不行，则用row_number()