当前位置： 首页 > news >正文

一道同分排名的SQL题

news 2025/8/19 21:38:17

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 |
+----------+--------+-------------+

3 小结

这种复杂的SQL是性能比较低的，且非常难维护和扩展，在工作中基本是禁止使用的。只是有些同事是SQL流的，总能写出一些很复杂的SQL，给后面的维护带来很大的不便，看不懂就改不了，好不容易看懂了也不太敢改。

这种SQL用来考试可能有点考智力的意思，即使懂JOIN、GROUP BY、HAVING、ORDER BY语句的知识，还得能够想出把排名转化为记录数才行。在工作中最好不用，用代码逻辑或者更好的设计来代替。

此外，现在AI工具比较多，问AI可以得到更多答案，每个AI都可能给出一个不同的方式、还不一定是对的，想挑战一下SQL知识的可以都试试。

http://www.xdnf.cn/news/1325035.html

相关文章：

  • Django开发Web应用
  • Dubbo 的SPI
  • 15.三数之和
  • vue3 el-table-column 列头添加 图标按钮
  • 使用websockets中的一些问题和解决方法
  • day25｜学习前端js
  • Day7--滑动窗口与双指针--1695. 删除子数组的最大得分，2958. 最多 K 个重复元素的最长子数组，2024. 考试的最大困扰度
  • JavaSE——高级篇
  • Java面试宝典：Redis 入门与应用
  • Poisson分布：稀有事件建模的理论基石与演进
  • 用随机森林填补缺失值：原理、实现与实战
  • 力扣hot100:移动零问题的巧妙解决：双指针与原地交换策略(283)
  • 开发避坑指南(28)：Spring Boot端点检查禁用失效解决方案
  • Vue3 中使用 Element Plus 完整指南
  • Spring AI Alibaba 项目接入兼容 OpenAI API 的大模型
  • 杂记 05
  • 母猪姿态转换行为识别：计算机视觉与行为识别模型调优指南
  • Android使用Kotlin协程+Flow实现打字机效果
  • Python 作用域 (scope) 与闭包 (closure)
  • 【学习嵌入式-day-27-进程间通信】
  • Docker常见指令速查
  • 用户认证技术
  • STL库——string（类函数学习）
  • SQL详细语法教程（六）存储+索引
  • AI心理助手开发文档
  • 在python中等号左边的都是对象，在matlab中等号a = 3+2 a就是个变量
  • 力扣hot100:盛最多水的容器：双指针法高效求解最大容量问题(11)
  • openfeign 只有接口如何创建bean的
  • Linux设备树简介
  • vue3入门-v-model、ref和reactive讲解