查询每个班级前几名

---

前言

提示：分组但不聚合，就是orcle中的窗口函数

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、数据准备

create table group_order_3_test(
`number_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class` varchar(255) NOT NULL COMMENT '班级',
  `student_id` int(11) NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`number_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试查询每个班级的前三名学生的成绩';

-- 插入数据
insert into group_order_3_test (class,student_id,score) values('三年一班',1,100);
insert into group_order_3_test (class,student_id,score) values('三年一班',2,120);
insert into group_order_3_test (class,student_id,score) values('三年一班',3,145);
insert into group_order_3_test (class,student_id,score) values('三年一班',4,112);
insert into group_order_3_test (class,student_id,score) values('三年一班',5,150);
insert into group_order_3_test (class,student_id,score) values('三年二班',6,100);
insert into group_order_3_test (class,student_id,score) values('三年二班',7,110);
insert into group_order_3_test (class,student_id,score) values('三年二班',8,112);
insert into group_order_3_test (class,student_id,score) values('三年二班',9,90);
insert into group_order_3_test (class,student_id,score) values('三年二班',10,80);
insert into group_order_3_test (class,student_id,score) values('三年二班',11,120);
insert into group_order_3_test (class,student_id,score) values('三年二班',12,100);
insert into group_order_3_test (class,student_id,score) values('三年二班',13,100);
insert into group_order_3_test (class,student_id,score) values('三年二班',14,100);
insert into group_order_3_test (class,student_id,score) values('三年二班',15,100);

二、查询

代码如下（示例）：

select * from group_order_3_test order by class asc ,score desc;

-- 查询每个年级前三名的学生
-- 这种查询能查出来前三名成绩没重复的学生
select class,student_id,score
from group_order_3_test a
where (select count(1) from group_order_3_test  where class = a.class and a.score < score) < 3
order by class,score desc ;

-- 每个学生在班级里的排名
select class,student_id,score,
(select count(1) from group_order_3_test where class = a.class and score>= a.score) as rank
from group_order_3_test a 
order by class,score desc ;

-- 查询每个班级的前三名，分数重复的算同一名次
select t1.* from group_order_3_test t1 
inner join 
    (
        -- 找出现在每个班级分数去重后的第四名
        select class,score from (
            -- 对所有数据班级，分数相同的数据去重，然后排出名次
            -- 和上面的每个学生在所在班级排名的思路一样
            select class,score,
            (select count(1) from (select distinct class,score from group_order_3_test order by class,score desc) b
            where b.class = a.class and b.score >= a.score) rn
            from 
            (select distinct class,score from group_order_3_test order by class,score desc) a 
        ) c where rn = 4
    ) t2 
on (t1.class = t2.class and t1.score >= t2.score)
order by t1.class,t1.score desc

---

总结