sql 分组后每组取第一个_mysql在分组后怎么取第二条(实现rank)

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