一道同分排名的SQL题

1 概述

遇到这样一道题：

(1) 有一张学生课程分数表，字段有：ID、名称、性别、科目、分数。（名称换为学号更能标识唯一学生，但名称好阅读，故这里先认为名称可以唯一标识学生。）

(2) 用一个SQL，分别统计每个学生多科目的总分，按性别输出前两名的学生和对应的总分。

这道题有两个难处理的点：

在一个SQL里需要分两类处理：按男女性别。
总分可能会同分，如果第一名有两个以上的人，那么需要输出多个人，就没有第二名了。如果第一名只有一个，第二名有多个，输出结果同样有多个，那么就没有第三及部分其它名次的人了。

2 解题

2.1 准备

先定义表结构，以MySQL 5.7为例：

-- DROP TABLE IF EXISTS`student_scores`;
CREATE TABLE IF NOT EXISTS `student_scores` (`id` BIGINT NOT NULL COMMENT 'id' AUTO_INCREMENT,`name` VARCHAR(64) COMMENT 'student name',`sex` VARCHAR(16) COMMENT 'student male or female',`subject` VARCHAR(64) COMMENT 'subject to learn',`score` INTEGER COMMENT 'score of subject',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='student scores';

2.2 查询SQL

这种SQL比较复杂，方法也不止一种，下面是一种较容易理解的：

SELECT a.name,a.sex,a.total_score
FROM (SELECT name,sex,SUM(score) AS total_scoreFROM student_scoresGROUP BY name, sex
) AS a
LEFT JOIN (SELECT name AS other_name,sex AS other_sex,SUM(score) AS other_totalFROM student_scoresGROUP BY name, sex
) AS b
ON a.sex = b.other_sex AND a.total_score < b.other_total
GROUP BY a.name, a.sex, a.total_score
HAVING COUNT(b.other_name) <= 1
ORDER BY a.sex DESC,a.total_score DESC;

分析：

(1) SQL里面第一个FROM后面的子查询和LEFT JOIN后面的子查询，结果是一样的，都是把每个学生的总分计算出来。只是为了容易区分，LEFT JOIN后面的子查询把结果的字段名重命名了一下。

SELECT name,               sex,              SUM(score) AS total_score FROM student_scores GROUP BY name, sex;
SELECT name AS other_name, sex AS other_sex, SUM(score) AS other_total FROM student_scores GROUP BY name, sex;

(2) 如果把上面子查询用一个临时表表示，SQL则可以简化：

CREATE TABLE IF NOT EXISTS `student_total_scores` (`id` BIGINT NOT NULL COMMENT 'id' AUTO_INCREMENT,`name` VARCHAR(64) COMMENT 'student name',`sex` VARCHAR(16) COMMENT 'student male or female',`total_score` INTEGER COMMENT 'score of subject',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='student total scores';-- 查询SQL简化为：
SELECT a.name,a.sex,a.total_score
FROM student_total_scores AS a
LEFT JOIN student_total_scores AS b
ON a.sex = b.other_sex AND a.total_score < b.other_total
GROUP BY a.name, a.sex, a.total_score
HAVING COUNT(b.other_name) <= 1
ORDER BY a.sex DESC,a.total_score DESC;

(3) 相当于一张表自关联查询，关联条件是：一要性别相同，二要主表的分数比关联表的分数要小。

这里要先理解LEFT JOIN语句的一些知识：

如果对于主表的一行记录，在关联表里面若有多行满足要求的记录，此时结果记录会把主表的这条记录关联出和满足要求的记录一样多的记录。
如果对于主表的一行记录，在关联表里面只有一行满足要求的记录、或者没有满足要求的记录，那么结果只有一条记录。

在这个基础上理解a.total_score < b.other_total条件，在同一个性别内，如果在关联表中有多行记录的分数是比当前行的分数高的，那么结果就有多行记录，这个行数N可以表示当前行分数排第(N+1)名。如果没有比当前行分数高的，那么当前行的分数就是第一名。注意，不管是否同分，只要分数高的就算一行。

对于HAVING COUNT(b.other_name) <= 1条件，当按name、sex、total_score进行group by之后，COUNT(b.other_name)实际就是计算关联出多少条记录，也就是比当前分数高的记录数。这个count为0代表第一名，为1代表第二名，为2代表第三名，以此类推。题目要求列前两名，所以这个count要小于等于1。

用例子理解一下：

场景一：假设在一个性别内，A为第一名，B和C并列第二名，D第四名，此时A行记录联查不到记录、count=0，B和C都只能联查到A只有一行记录、count=1，D联查到ABC有三行记录、count=3，所以结果列出的是ABC。如果第二名并列更多人，它们的记录count仍然为1、符合输出条件，D的count则变得更大、不满足输出条件。
场景二：假设在一个性别内，AB并列第一名，C并列第三名，D第四名，此时A和B行记录联查不到记录、count=0，C联查到A和B有两条记录、count=2，D联查到ABC有三行记录、count=3，所以结果列出的是AB。如果第一名并列更多人，它们的count仍然为0、符合输出条件，C和D的count会变大、不满足输出条件。
场景三：假设在一个性别内，A为第一名，B为第二名，C为第三名，D第四名，此时A行记录联查不到记录、count=0，B行记录联查到一行记录、count=1，C行记录联查到两行记录、count=2，D行记录联查到3行记录、count=3。满足输出条件的为A和B。

2.3 测试

这里只列一下场景一“并列第二名”的测试，其它的可以修改一下数据同样验证：

insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1001, 'zhangsan', 'male', 'yuwen', 84);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1002, 'zhangsan', 'male', 'shuxue', 98);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1003, 'zhangsan', 'male', 'yingyu', 99);insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1006, 'lisi', 'female', 'yuwen', 90);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1007, 'lisi', 'female', 'shuxue', 66);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1008, 'lisi', 'female', 'yingyu', 89);insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1011, 'wangwu', 'female', 'yuwen', 77);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1012, 'wangwu', 'female', 'shuxue', 69);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1013, 'wangwu', 'female', 'yingyu', 93);insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1016, 'chenliu', 'male', 'yuwen', 95);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1017, 'chenliu', 'male', 'shuxue', 99);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1018, 'chenliu', 'male', 'yingyu', 88);insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1021, 'huangqi', 'male', 'yuwen', 82);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1022, 'huangqi', 'male', 'shuxue', 100);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1023, 'huangqi', 'male', 'yingyu', 99);insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1026, 'liuba', 'female', 'yuwen', 85);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1027, 'liuba', 'female', 'shuxue', 79);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1028, 'liuba', 'female', 'yingyu', 66);insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1031, 'zhaojiu', 'male', 'yuwen', 90);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1032, 'zhaojiu', 'male', 'shuxue', 92);
insert into student_scores(`id`, `name`, `sex`, `subject`, `score`) values(1033, 'zhaojiu', 'male', 'yingyu', 80);-- 表记录结果：
+------+----------+--------+---------+-------+
| id   | name     | sex    | subject | score |
+------+----------+--------+---------+-------+
| 1001 | zhangsan | male   | yuwen   |    84 |
| 1002 | zhangsan | male   | shuxue  |    98 |
| 1003 | zhangsan | male   | yingyu  |    99 |
| 1016 | chenliu  | male   | yuwen   |    95 |
| 1017 | chenliu  | male   | shuxue  |    99 |
| 1018 | chenliu  | male   | yingyu  |    88 |
| 1021 | huangqi  | male   | yuwen   |    82 |
| 1022 | huangqi  | male   | shuxue  |   100 |
| 1023 | huangqi  | male   | yingyu  |    99 |
| 1031 | zhaojiu  | male   | yuwen   |    90 |
| 1032 | zhaojiu  | male   | shuxue  |    92 |
| 1033 | zhaojiu  | male   | yingyu  |    80 |
| 1006 | lisi     | female | yuwen   |    90 |
| 1007 | lisi     | female | shuxue  |    66 |
| 1008 | lisi     | female | yingyu  |    89 |
| 1011 | wangwu   | female | yuwen   |    77 |
| 1012 | wangwu   | female | shuxue  |    69 |
| 1013 | wangwu   | female | yingyu  |    93 |
| 1026 | liuba    | female | yuwen   |    85 |
| 1027 | liuba    | female | shuxue  |    79 |
| 1028 | liuba    | female | yingyu  |    66 |
+------+----------+--------+---------+-------+

看一下总分的结果：

+----------+--------+-------------+
| name     | sex    | total_score |
+----------+--------+-------------+
| huangqi  | male   |         281 |
| zhangsan | male   |         281 |
| chenliu  | male   |         282 |
| zhaojiu  | male   |         262 |
| lisi     | female |         245 |
| wangwu   | female |         239 |
| liuba    | female |         230 |
+----------+--------+-------------+

对于sex=male的数据，第一名为282分，第二名281分并列有两个，第四名为262分。sex=female的数据，则前三名各一个。

完整SQL输出的结果：

+----------+--------+-------------+
| name     | sex    | total_score |
+----------+--------+-------------+
| chenliu  | male   |         282 |
| huangqi  | male   |         281 |
| zhangsan | male   |         281 |
| lisi     | female |         245 |
| wangwu   | female |         239 |
+----------+--------+-------------+