目标
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 使用group by
进行分组 -> 不合理, 分组后的聚合函数并不能提供有用信息
1.2 使用order by
将所有列按同个课程按顺序堆起来,隐性得分了个组,逻辑上并没有对数据操作select * from score order by c_id;
- 分解问题
同个课程内如何根据分数排名 - 排名算法
同个课程中,比A同学成绩高的同学有N名,则A同学的排名就为N + 1名
group by 的注意事项
- 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 相当于隐性得做了一次分组
- 子查询的算法解析
扫描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, 名次
;