【MySQL】伪rank函数的需求实现，使用MySQL变量

目标

MySql 没有rank函数，可以使用变量完成rank逻辑

题目ddl

-- 成绩表 持有学生表和课程表的主键
CREATE TABLE `Score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);
-- 学生表
CREATE TABLE `Student`(
	`s_id` VARCHAR(20),
	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
	PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
	`c_id`  VARCHAR(20),
	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
	`t_id` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`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);

题目

查询学生平均成绩及其名次

语法储备

• @i定义一个变量
• @i := 5 将5赋值给变量i
• @i := @i + 1 每次调用i都先自增1再返回（每次叠加1）
• @avg := avg_s ...... select ... ROUND(AVG(s_score),2) AS avg_s
  将子查询列avg_s的每一行的值赋值给@avg
• SELECT @avg_score:=0,@i:=0,@k:=0 生成一个中间表
  

变量的实际作用

生成中间表，扫描中间表的时候，可以从上之下动态存储定义好的值，用于输出最终表。

SELECT 
    * 
FROM  
    (
      SELECT s_id,ROUND(AVG(s_score),2) AS avg_s FROM score GROUP BY s_id ORDER BY avg_s DESC
    )a, -- 生成根据平均分排名的的临时表
    (
      SELECT @avg_score:=0,@i:=0,@k:=0 -- 新建一个含有三个自定义字段的临时表
    )b; -- 隐式的a, b表连接, b表就一条记录

使用变量的解题

-- 使用@i 的语法
SELECT a.s_id,
        @i:=@i+1 AS '不考虑总人数的排名', -- 对临时表的字段进行赋值, 不考虑总人数的递增序列
        @k:=(CASE WHEN @avg_score=a.avg_s THEN @k ELSE @i END) AS '考虑总人数的排名', -- 考虑总人数的递增序列
        @avg_score:=avg_s AS '平均分' -- 【重点】寄存用的变量，两个平均分连续的序列将合并排名
FROM  (
        SELECT s_id,ROUND(AVG(s_score),2) AS avg_s FROM score GROUP BY s_id ORDER BY avg_s DESC
      )a, -- 生成根据平均分排名的的临时表
      (
        SELECT @avg_score:=0,@i:=0,@k:=0 -- 新建一个含有三个自定义字段的临时表
      )b; -- 隐式的a, b表连接

不使用变量的题解

SELECT temp2.*, (COUNT(temp1.平均成绩) + 1) AS 名次 FROM
(
    SELECT sc.s_id ,SUM(s_score)/COUNT(1) AS 平均成绩 FROM score sc
    GROUP BY sc.s_id ORDER BY 平均成绩 DESC 
) temp1 RIGHT JOIN
(
    SELECT sc.s_id ,SUM(s_score)/COUNT(1) AS 平均成绩 FROM score sc
    GROUP BY sc.s_id ORDER BY 平均成绩 DESC
) temp2 
ON temp1.平均成绩 > temp2.平均成绩
GROUP BY temp2.s_id
ORDER BY 名次;
;

不使用变量的思路