MySQL取各个组中排名前几的记录

1. 需求

按课程分组，按分数降序，取每门课程分数最高的两条。

这是一种分组等级实现的应用。

2. 数据准备

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `score` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='学生成绩表';

INSERT INTO `score` VALUES 
(1,1,1,1,66),(2,2,1,1,77),(3,3,1,1,88),(4,1,2,1,71),(5,2,2,1,61),(6,3,2,1,64);

id	student_id	course_id	class_id	score
1	1	1	1	66
2	2	1	1	77
3	3	1	1	88
4	1	2	1	71
5	2	2	1	61
6	3	2	1	64

3. 具体实现

第一种玩法：（利用表自关联）

主要思路：
通过表自关联，关联条件是课程id和分数，其中根据分数关联是关键点，利用左表的分数小于等于右表的分数，从而可以统计出大于等于该分数的有多少个，确定一个等级。

比如拿左表的66分，便可关联到右表的66、77、88，从而确定等级为3；拿左表的77分，便可关联到右表的77、88，从而确定等级为2；拿左表的88分，便可关联到右表的88，从而确定等级为1。

其中，课程id便是用来分组的字段、分数是用来组内排序确定等级的字段。

select t1.* from score t1,(
SELECT 
    s1.course_id , s1.score, COUNT(1) as rank
FROM
    score s1
        LEFT JOIN
    score s2 ON s1.course_id = s2.course_id
        AND s1.score <= s2.score
GROUP BY s1.course_id , s1.score
order by s1.course_id,s1.score desc

) t2
where t1.course_id=t2.course_id and t1.score=t2.score and t2.rank <= 2
order by t1.course_id,t1.score desc;

id	student_id	course_id	class_id	score
2	2	1	1	77
3	3	1	1	88
4	1	2	1	71
6	3	2	1	64

另一种玩法：（利用变量）

主要思路：

定义两个变量，课程id变量@cid设置为空，等级变量@rank设置为0。

通过 order by t1.course_id,t1.score desc 将结果集排好序。

然后解析每条记录，

先解析第一条记录，进入条件判断，如何如果course_id等于变量@cid，就让等级变量@rank加1，否则就初始化为1。因为一开始@cid是空，所以在第一条记录的时候，等级变量@rank会初始化为1 。

条件判断结束后，将course_id赋值给变量@cid ，记住是在判断后给变量@cid赋值，所以下一条记录判断时的变量@cid是上一条记录的course_id。

然后解析第二条记录的时候，变量@cid就有值了，@cid与course_id都为1，所以会让@rank加1 。

当 course_id为2的时候，@cid还是上一条记录赋值的course_id=1，这个时候条件不成立，就让@rank又初始化为1，从而实现了每一个组的等级划分。

select t1.* from score t1,(
select 
    if(@cid=s.course_id,@rank:=@rank+1,@rank:=1) as rank,
    @cid:=s.course_id as course_id,
    s.score
from score s,(select @cid:=null,@rank:=0) r 
order by s.course_id,s.score desc
) t2 
where t1.course_id=t2.course_id and t1.score=t2.score and t2.rank <= 2
order by t1.course_id,t1.score desc;