如何编写强制包含子查询的SQL,获取每门课程最高分对应的学生ID
核心查询需求
- 业务目标:返回每门所选课程中取得最高成绩的学生的
student_id,本质是匹配单课程维度下成绩最优的对应学生 - 强制约束:编写的SQL语句必须包含子查询,不得使用无嵌套的单层查询实现
- 参考表结构逻辑:核心关联表为成绩表
grade,存储每条选课记录对应的student_id、course_id、grade字段
已尝试语句的问题复盘
第一版SQL的错误点
首次编写的语句:
select student_id from Student s where s.course_id in (select course_id, max(grade) from course c, grade g where g.course_id = c.course_id group by course_id)
跑不通的核心原因:
- 子查询返回了
course_id、max(grade)两个字段,但外层IN判断仅支持单值匹配,字段数不匹配会直接触发语法报错 - 逻辑上也没有完成目标:该语句仅能筛选“选了某门课、且这门课存在最高分记录”的学生,无法把学生自身的成绩和对应课程的最高分做绑定,根本筛选不出真正拿了课程最高分的人
第二版SQL的问题
调整逻辑后编写的语句:
select student_id from Student s where s.course_id in (select course_id from grade g1 where g1.grade = (select max(grade) from grade g where g.course_id = s.course_id)
存在的问题:
- 语法层面:末尾少了两个右括号,分别对应内层max子查询的条件结束、外层IN子句的结束,直接执行会报语法错误
- 逻辑层面:主查询从纯存储学生基础信息的
Student表取数,该表本身不存储成绩字段,无法直接和成绩的最高分做匹配,相当于绕了远路还容易出现关联偏差
符合约束的可运行实现
下面的版本完全满足“必须带子查询”的要求,且能正确筛选每门课的最高分学生:
SELECT DISTINCT student_id, course_id FROM grade g_outer WHERE grade = ( -- 关联子查询:逐行匹配当前记录所属课程的最高分 SELECT MAX(grade) FROM grade g_inner WHERE g_inner.course_id = g_outer.course_id )
实现说明:
- 整段语句嵌套了两层相关子查询,符合强制约束要求
- 关联条件严格按课程维度匹配最高分,不会出现跨课程错配的问题
- 加
DISTINCT是为了兼容同课程多个学生同分拿第一的场景,避免重复输出记录
子查询实用编写技巧
- 写
IN类子查询时,必须保证子查询返回的字段数和外层IN要求的字段数完全一致,多带、少带字段都会直接触发语法错误 - 使用相关子查询(即子查询中引用外层表字段的逻辑)时,一定要把维度匹配条件写全:比如要算“当前行所属课程的最高分”,关联条件必须明确写成
内层.course_id = 外层.course_id,遗漏匹配维度会把逻辑变成“和全局最高分匹配”,完全偏离业务目标 - 自查子查询逻辑的快速方法:把外层表某一行的具体值带到内层子查询单独执行,看返回结果是否符合预期。比如某门课
course_id = 3的实际最高分是92,把这个id带到max(grade)的子查询里,验证返回值是不是92,就能快速判断关联逻辑是否正确 - 写极值匹配类需求时,优先从存储核心关联字段的表取数:比如这个需求里
grade表同时存了学生、课程、成绩三个核心字段,从grade表做主查询源,比从纯基础信息的学生表、课程表取数要顺畅很多,不用写冗余的JOIN关联
内容的提问来源于stack exchange,提问作者romhayh
相关产品推荐
相关产品推荐

