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;

文章作者: 叶遮沉阳
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 叶遮沉阳 !
  目录