a1617d8388fae69f72dd1bbd616df8c2.png
需求: 每个班取出成绩排第二的同学
排第一的在分组排序后很容易得到,但取排第二的就需要rank排序,而mysql没有内置的rank函数,要实现这个需求,就得自己实现
建表语句如下
CREATE TABLE `score_info` (
`class` varchar(20) DEFAULT NULL,
`student_name` varchar(20) DEFAULT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '张三', '80');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '李四', '78');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '王五', '90');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '李长青', '90');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '蔡国华', '80');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '赵倩', '76');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('3班', '张萌萌', '70');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('3班', '李子天', '88');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('3班', '欧阳锦', '69');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '朱晓光', '79');
最终的查询语句为
SELECT a.class,a.student_name,score from (
SELECT
SCORE_INFO.*,
IF(@last_class=class,@rank := @rank+1,@rank:=1) as "rank",
@last_class := class
FROM SCORE_INFO,(SELECT @rank := 0,@last_class:="") r
ORDER BY class,score DESC
) a
WHERE rank=2
首先,我们通过 order by class ,score desc先对表进行按班级升序、分数降序,然后我们只需要计算每条记录的名次 rank 值即可。
其中,我们通 select @last_class:= '' , @rank:=0 定义了两个变量
@last_class 用于记录上一条数据的班级值,初始化为 空字符
@rank 用于计算学生成绩的名次,初始化为 0
计算排名值语句 case when class = @last_class then @rank:=@rank+1 else @rank:=1 end as rank表示
如果当前记录的 班级值(class)与 上一条数据的班级值(@last_class)相等,那么这两条数据的班级相同,排名为 @rank + 1
如果当前记录的 班级值(class)与 上一条数据的班级值(@last_class)不相等,那么当前记录属于新的班级,排名为 1
最后,在每条记录计算完名次之后,我们需要更新 @last_class 的值,将当前记录的班级赋值给 @last_class,即 @last_class:=class
思考:如何处理相同的排名,比如11122233这种
我写的sql是:SELECT a.* from (
SELECT
`order`.*,
IF(@last_class=DATE_FORMAT(create_time,'%Y-%m'),@rank := @rank+1,@rank:=1) as "rank",
@last_class := DATE_FORMAT(create_time,'%Y-%m')
FROM `order`,(SELECT @rank := 0,@last_class:="") r
ORDER BY DATE_FORMAT(create_time,'%Y-%m'),create_time DESC
) a
WHERE rank=2
对应的sql脚本下载位置:
链接:https://pan.baidu.com/s/11cj8wRZUWwhIMhlQZz2utw
提取码:b8v2