---

前言

提示：分組但不聚合，就是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

---

總結