MySQL排名与分组的相关实例与解析~~~浅显易懂版

1 数据

数据生成代码

--------------------------------创建表、插入数据----------------------------------
--成绩表
CREATE TABLE `Score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

2 排名

2.1 普通排名

~不分组，直接对分数进行排名，分数相同不并列

SELECT  s_id AS "学生ID",
		s_score,
		@r :=  @r + 1 AS rank
FROM score s,(SELECT @r := 0) r
ORDER BY s_score DESC;

其中@r := @r + 1；SELECT @r := 0表示生成序列号

• SELECT @r := 0 表示初始化序列号为 0
• @r := @r + 1 表示序列号按 1 递增

2.2 普通并列排名~1123型并列

~直接对分数进行排名，相同分数排名并列

SELECT s_id AS "学生ID",s_score,
    CASE WHEN @prerank = s_score THEN @i
         WHEN @prerank := s_score THEN @i := @i + 1
    END AS rank         
FROM score s,(SELECT @prerank := NULL,@i := 0) r
ORDER BY s_score DESC;

= 与 := 的区别

• = ：只有在 set 和 update 中时才和 := 一样表示赋值的作用，其他时候都是等于的作用。
• := ：在任何时候都表示赋值的作用。

2.3 普通并列排名~1134型并列

函数	含义
IF(expr,v1,v2)	如果expr表达式为true，返回v1，否则返回v2

SELECT s_id AS "学生ID",s_score,rank
FROM
    (SELECT s.*,
            @c := IF(@p=s_score,@c,@r) AS rank,
            @p := s_score,
            @r := @r +1
    FROM score s,(SELECT @c:=0,@p:=NULL,@r:=1) r
    ORDER BY s_score DESC) subquery;

代码解析

• @c := IF(@p=s_score,@c,@r) AS rank 表示新增一列变量 rank ，若p=s_score，则 c := c ；否则 c := r
• @p := s_score 表示 将 p 赋值为 s_score
• @r := @r +1 表示 将 r 赋值为 r+1
• (SELECT @c:=0,@p:=NULL,@r:=1) 表示初始化 c p r 的值
  

3. 分组排名

3.1 分组后组内排名~普通顺序排名

SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
    (SELECT s.*,
            IF(@c=c_id,@r:=@r+1,@r:=1) AS rank,
            @c:=c_id
    FROM score AS s,(SELECT @c:=NULL,@r:=0) AS r
    ORDER BY c_id,s_score DESC) AS subquery;

3.2 分组后组内排名~1123型并列排名

SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
    (SELECT s.*,
            IF( @c=c_id,
                CASE
                    WHEN @p=s_score THEN @r
                    WHEN @p:=s_score THEN @r:=@r+1
                END,
                @r:=1) AS rank,
            @c:=c_id,
            @p:=s_score
    FROM score AS s,
        (SELECT @c:=NULL,@p:=NULL,@r:=0) AS r
    ORDER BY c_id,s_score DESC) AS subquery;

3.3 分组后组内排名~1134型并列排名

3.4.1 含bug版代码

#一段结果正确但有bug的代码
SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
    (SELECT s.*,
            IF( @c=c_id,@k:=@k+1,@k:=1),
            CASE
                WHEN @p=s_score THEN @r
                WHEN @p:=s_score THEN @r:=@k
            END AS rank,
            @c:=c_id            
    FROM score AS s,
        (SELECT @c:=NULL,@p:=NULL,@r:=0,@k:=0) AS r
    ORDER BY c_id,s_score DESC) AS subquery;

###上例代码有个bug，若01最低分与02最高分相同就会出错

#新建score表代码
--成绩表
CREATE TABLE `Score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);

--成绩表测试数据
insert into Score values('01' , '01' , 99);
insert into Score values('01' , '02' , 90);
insert into Score values('02' , '01' , 98);
insert into Score values('02' , '02' , 90);
insert into Score values('03' , '01' , 98);
insert into Score values('03' , '02' , 80);
insert into Score values('04' , '01' , 90);
insert into Score values('04' , '02' , 81);

3.4.2 修改版代码

SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
    (SELECT s.*,
            IF( @c=c_id,@k:=@k+1,@k:=1),
            IF( @c=c_id,
                CASE
                    WHEN @p=s_score THEN @r
                    WHEN @p:=s_score THEN @r:=@k
                END,
                @r:=1) AS rank,
            @c:=c_id,
            @p:=s_score
    FROM score AS s,
        (SELECT @c:=NULL,@p:=NULL,@r:=0,@k:=0) AS r
    ORDER BY c_id,s_score DESC) AS subquery;

参考：
https://www.cnblogs.com/niniya/p/9046449.html
https://blog.csdn.net/csdnluolei/article/details/83507312