SQL面试题挑战13:分组topN
问题
问题:下面是某个班级的成绩表,需要筛选出每个科目前2名的学生信息。如果分数一样,名次是并列的,后面的同学名次就不连续。比如有2个同学是第一名,那么下一个同学的名次就是第3名,呈现1,1,3的名次排列。
stu_id stu_name subject score
1001 张三 语文 90
1001 张三 数学 80
1001 张三 英语 70
1002 李四 语文 90
1002 李四 数学 75
1002 李四 英语 80
1003 王五 语文 80
1003 王五 数学 70
1003 王五 英语 60
建表语句
drop table if exists scores;
create table scores(
stu_id varchar(20),
stu_name varchar(20),
subject varchar(10),
score decimal(10,2)
);
insert into scores values
('1001','张三', '语文',90),
('1001','张三', '数学',80),
('1001','张三', '英语',70),
('1002','李四', '语文',90),
('1002','李四', '数学',75),
('1002','李四', '英语',80),
('1003','王五', '语文',80),
('1003','王五', '数学',70),
('1003','王五', '英语',60)
解答
思路:
使用rank即可
完整查询如下
select
*
from
(
select
stu_id,
stu_name,
subject,
score,
rank() over (partition by subject order by score)rk
from
scores
)t
where
rk<3