【MySQL】伪rank函数的需求实现，group by 的使用习惯

题目来源：https://blog.csdn.net/fashion2014/article/details/78826299

目标

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);

题目

按各科成绩进行排序，并显示排名
【不考虑总人数】【1, 1, 2, 3】

思路：

1. 是否要分组 -> 各科成绩排序， 所以分组可能是必要的
   1.1 使用group by 进行分组 -> 不合理, 分组后的聚合函数并不能提供有用信息
   1.2 使用order by 将所有列按同个课程按顺序堆起来，隐性得分了个组，逻辑上并没有对数据操作
   select * from score order by c_id;
   

2. 分解问题
   同个课程内如何根据分数排名
   

3. 排名算法
   同个课程中，比A同学成绩高的同学有N名，则A同学的排名就为N + 1名

   group by 的注意事项

   • sql 的执行顺序：from —> where —> group by —> select —> having —> order by —> limit （select在group之后）
   • select 列A，
     select 列层次上的子查询

   SELECT 
       * ,(
           SELECT 
               COUNT(DISTINCT s_score) + 1 
           FROM 
               score sc2
           WHERE 
               sc2.c_id = sc1.c_id  
               -- 同个课程中，大于自身成绩有N个，则为第N + 1名。
               -- 忽略等于条件，相当于合并所有相等的名次
               AND sc2.s_score > sc1.s_score 
           ) AS 名次 
   FROM 
       score sc1
   ORDER BY 
       sc1.c_id, 名次;  -- order by A.c_id 相当于隐性得做了一次分组
   
   

4. 子查询的算法解析
   扫描sc1至第一行时，未count（）时执行的sql语句可以等效为

   SELECT * FROM score sc, score sc2 WHERE sc2.c_id = '01' AND sc.c_id = '01' AND sc2.s_score > 76
   

   
   可以发现有01学生和03学生同为80分，题意是不考虑总人数的情况排名，所以要将并列的成绩算为1个，最快捷的做法就是COUNT(DISTINCT s_score) 或者 COUNT(DISTINCT c_id)

---

变形题

按各科成绩进行排序，并显示排名
【考虑总人数】 【1, 1, 3, 4】

SELECT 
    -- 为什么是B的score？？ --> null 值起作用了， count(null) = 0, count(0) = 1
    sc1.*, COUNT(sc2.s_score) + 1 AS 名次 
FROM 
    -- 若成绩 = 自身（包括自身） 用null来标记
    score sc1 LEFT JOIN score sc2 -- 连接条件是两个
    ON sc1.c_id = sc2.c_id  -- 同个课程内比较
    AND sc1.s_score < sc2.s_score -- 同个课程内分数大于自身的
GROUP BY  
    -- 用于聚合函数的正确使用
    -- 宏观语义上，'01' 课程中 A同学的成绩 有 N 次小于其他人的成绩 课程id 和 学生id 应该成为分组标记 
    -- 分组之后可以屏蔽 s_score 的影响 
    sc1.c_id, sc1.s_id, sc1.s_score  
ORDER BY 
    sc1.c_id, 名次  
;