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

一道同分排名的SQL题

news 2025/8/19 8:57:25

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.lryc.cn/news/624629.html

相关文章：

  • Vue深入组件：组件 v-model 详解2
  • Windows从零到一安装KingbaseES数据库及使用ksql工具连接全指南
  • DSP音频算法工程师技能2
  • PPT生成视频的AI大模型应用技巧
  • 如何区分网站使用的是Vue2还是Vue3
  • 电梯的构造|保养|维修视频全集_电梯安全与故障救援（课程下载）
  • 计算机视觉 图像处理 在两张二值图中检测线条交集点的高效方法 适合工程图纸比对、生物神经元网络分析和文档特征提取等场景 ，
  • 数据仓库理论
  • 什么叫做 “可迭代的产品矩阵”？如何落地？​
  • 密码管理中随机数安全修复方案
  • 飞算JavaAI家庭记账系统：从收支记录到财务分析的全流程管理方案
  • GISer大事件，保研考研竞赛时间线一览
  • echarts实现3个y轴的图表
  • Mysql核心框架知识
  • 深度剖析PyTorch分布式训练：从原理到工程实践
  • 后端通用基础代码
  • AC3 用户认证技术
  • 用一个label控件随便显示一些字(用矢量字库)，然后用anim动画动态设置lable位置
  • Read Frog：一款开源AI浏览器语言学习扩展
  • JVM 面试精选 20 题
  • 项目中如何分配资源，以避免资源分配不均
  • 【Linux操作系统】简学深悟启示录：进程状态优先级
  • 电子元器件-电容终篇：基本原理、参数解读、电路作用、分类及区别、应用场景、选型、降频及实战案例
  • 如何在服务器 clone github 项目
  • openEuler系统备份与恢复方法
  • 8.18决策树
  • B站 韩顺平 笔记 （Day 22）
  • 芋道审批流配置流程表单超详细介绍
  • 《清华级防护，了解一下？》
  • 龙石数据中台 V3.7.1 升级 | 一站式完成数据可视化