数据库子查询题集10题以下题目基于典型的“学生-课程-成绩”数据库模式并引入复杂场景、性能陷阱及高级SQL特性旨在深入考察对子查询执行原理、优化策略及逻辑转换的理解。题号题目描述考察核心1多层嵌套与聚合查询“平均成绩高于全校学生平均成绩且其最高单科成绩高于该科目平均分20分以上”的学生的学号和姓名。多层聚合子查询、HAVING子句中的复杂条件。2相关子查询与极值找出每门课程中成绩不是最高也不是最低的学生名单即排除每门课的最高分和最低分获得者。相关子查询、NOT IN与极值查询MAX,MIN的结合。3EXISTS 与 三表关联查询从未选修过“张老师”所授课程但选修过所有“李老师”所授课程的学生信息。EXISTS,NOT EXISTS与全称量词的逻辑转换通过双重否定或计数实现。4ALL/ANY 与除法运算查询选修了计算机系开设的全部课程的学生学号。使用NOT EXISTS模拟关系代数中的“除法”运算。5标量子查询与更新将每位学生的总成绩更新到学生表的一个新增字段total_score中要求使用单条UPDATE语句配合标量子查询完成。在UPDATE的SET子句中使用相关标量子查询。6FROM 子句中的子查询与窗口函数查询每个班级中总成绩排名前10%的学生。要求使用FROM子句中的子查询先计算排名和百分比再筛选。子查询生成派生表Derived Table并与窗口函数RANK(),NTILE()结合。7条件子查询与 CASE 表达式生成一份报告列出所有学生。对于每个学生如果其有任何一门课成绩低于60分则显示“有不及格”如果所有成绩都在85分以上则显示“优秀”否则显示“普通”。在SELECT子句和CASE WHEN中使用EXISTS和聚合子查询。8横向连接 (LATERAL JOIN) 问题对于每个学生找出其成绩最高的两门课程的名称和分数。使用LATERAL子查询或特定数据库的等效语法实现行间关联。9集合运算与子查询性能查询本学期有成绩记录但上学年没有任何成绩记录的学生。分别用NOT IN、NOT EXISTS和LEFT JOIN...IS NULL三种方法实现并分析其执行计划可能存在的差异。不同逻辑写法对数据库优化器如He3DB的子连接上拉的影响及性能对比。10递归子查询CTE给定一个“员工-经理”表(employee_id, manager_id, salary)找出每个员工的所有间接下属不限层级中薪资最高的那位员工的薪资。使用递归公共表表达式CTE遍历树形结构并在外层查询中进行聚合。部分难题的参考解答与深度解析题目 2相关子查询与极值-- 方法找出每门课成绩既不是最大值也不是最小值的学生 SELECT DISTINCT s.student_id, s.name, c.course_name, sc.score FROM student s JOIN score sc ON s.student_id sc.student_id JOIN course c ON sc.course_id c.course_id WHERE sc.score ( SELECT MAX(score) FROM score sc2 WHERE sc2.course_id sc.course_id -- 相关子查询找当前课程最高分 ) AND sc.score ( SELECT MIN(score) FROM score sc3 WHERE sc3.course_id sc.course_id -- 相关子查询找当前课程最低分 );解析此查询为每个成绩行执行了两次相关子查询以获取当前课程的极值可能产生性能问题。优化器可能尝试将其转换为JOIN但相关性子查询限制了上拉优化。题目 4ALL/ANY 与除法运算使用 NOT EXISTS 实现-- 查询选修了计算机系全部课程的学生 SELECT s.student_id FROM student s WHERE NOT EXISTS ( -- 找出计算机系开设的、该学生没选的课程 SELECT c.course_id FROM course c WHERE c.dept 计算机系 AND NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id AND sc.course_id c.course_id ) );解析这是经典的关系除法问题的NOT EXISTS解法。它通过双重否定“不存在一门计算机系的课是他没选的”来表达全称量词。外层查询遍历学生内层第一重NOT EXISTS检查是否有一门计算机系的课不在该学生的选课列表中由最内层NOT EXISTS判断。这种嵌套相关子查询是优化器的重点优化对象在He3DB等数据库中可能会被重写为更高效的semijoin或antijoin。题目 9集合运算与性能分析-- 方法1: NOT IN (需注意NULL值问题) SELECT student_id FROM score WHERE term 2024-春 AND student_id NOT IN ( SELECT student_id FROM score WHERE term 2023-秋 ); -- 方法2: NOT EXISTS (通常更优) SELECT s.student_id FROM score s WHERE s.term 2024-春 AND NOT EXISTS ( SELECT 1 FROM score s2 WHERE s2.term 2023-秋 AND s2.student_id s.student_id ); -- 方法3: LEFT JOIN ... IS NULL SELECT DISTINCT s.student_id FROM score s LEFT JOIN score s_prev ON s.student_id s_prev.student_id AND s_prev.term 2023-秋 WHERE s.term 2024-春 AND s_prev.student_id IS NULL;性能分析NOT IN如果子查询返回结果集包含NULL值则整个NOT IN条件可能返回UNKNOWN即无结果这是逻辑陷阱。性能上数据库可能将其实现为Hash Anti Join。NOT EXISTS是表达此逻辑最清晰和安全的方式通常能利用关联字段的索引。现代数据库优化器如MySQL 8.0、He3DB擅长将NOT EXISTS重写为高效的antijoin。LEFT JOIN...IS NULL这是一种更“过程化”的写法明确指示数据库先进行连接操作。在拥有良好统计信息时优化器可能生成与NOT EXISTS相似的执行计划。但在复杂情况下它可能比NOT EXISTS产生更大的中间结果集。核心优化点数据库优化器在查询重写阶段会尝试将子查询特别是EXISTS/IN类“上拉”为JOIN以减少嵌套迭代。例如He3DB会将满足条件的EXISTS_SUBLINK转换为semijoin将NOT EXISTS转换为antijoin从而可以利用高效的连接算法如Hash Join和并行执行。能否成功上拉取决于子查询是否“相关”引用外层列以及其复杂程度。题目 10递归子查询CTEWITH RECURSIVE SubordinateTree AS ( -- 锚点直接下属 SELECT employee_id, manager_id, salary FROM employee UNION ALL -- 递归间接下属 SELECT st.employee_id, e.manager_id, e.salary FROM SubordinateTree st JOIN employee e ON st.manager_id e.employee_id ) SELECT emp.employee_id, (SELECT MAX(salary) FROM SubordinateTree st WHERE st.employee_id emp.employee_id) as max_subordinate_salary FROM employee emp;解析此CTE先递归构建出每个员工的所有下属关系包括间接。外层查询通过一个标量子查询从递归结果中找出每个员工下属的最高薪资。递归CTE是解决层次查询的强有力工具但其性能依赖于递归深度和表的索引情况。参考来源大云海山数据库(He3DB) 内核分析-子查询优化PTA沈师数据库原理——DB10_SQL实验题MySQL查询之子查询详解从执行原理到深度优化数据库子查询数据库子查询数据仓库原理与实践课程指南
高阶子查询题目精炼
数据库子查询题集10题以下题目基于典型的“学生-课程-成绩”数据库模式并引入复杂场景、性能陷阱及高级SQL特性旨在深入考察对子查询执行原理、优化策略及逻辑转换的理解。题号题目描述考察核心1多层嵌套与聚合查询“平均成绩高于全校学生平均成绩且其最高单科成绩高于该科目平均分20分以上”的学生的学号和姓名。多层聚合子查询、HAVING子句中的复杂条件。2相关子查询与极值找出每门课程中成绩不是最高也不是最低的学生名单即排除每门课的最高分和最低分获得者。相关子查询、NOT IN与极值查询MAX,MIN的结合。3EXISTS 与 三表关联查询从未选修过“张老师”所授课程但选修过所有“李老师”所授课程的学生信息。EXISTS,NOT EXISTS与全称量词的逻辑转换通过双重否定或计数实现。4ALL/ANY 与除法运算查询选修了计算机系开设的全部课程的学生学号。使用NOT EXISTS模拟关系代数中的“除法”运算。5标量子查询与更新将每位学生的总成绩更新到学生表的一个新增字段total_score中要求使用单条UPDATE语句配合标量子查询完成。在UPDATE的SET子句中使用相关标量子查询。6FROM 子句中的子查询与窗口函数查询每个班级中总成绩排名前10%的学生。要求使用FROM子句中的子查询先计算排名和百分比再筛选。子查询生成派生表Derived Table并与窗口函数RANK(),NTILE()结合。7条件子查询与 CASE 表达式生成一份报告列出所有学生。对于每个学生如果其有任何一门课成绩低于60分则显示“有不及格”如果所有成绩都在85分以上则显示“优秀”否则显示“普通”。在SELECT子句和CASE WHEN中使用EXISTS和聚合子查询。8横向连接 (LATERAL JOIN) 问题对于每个学生找出其成绩最高的两门课程的名称和分数。使用LATERAL子查询或特定数据库的等效语法实现行间关联。9集合运算与子查询性能查询本学期有成绩记录但上学年没有任何成绩记录的学生。分别用NOT IN、NOT EXISTS和LEFT JOIN...IS NULL三种方法实现并分析其执行计划可能存在的差异。不同逻辑写法对数据库优化器如He3DB的子连接上拉的影响及性能对比。10递归子查询CTE给定一个“员工-经理”表(employee_id, manager_id, salary)找出每个员工的所有间接下属不限层级中薪资最高的那位员工的薪资。使用递归公共表表达式CTE遍历树形结构并在外层查询中进行聚合。部分难题的参考解答与深度解析题目 2相关子查询与极值-- 方法找出每门课成绩既不是最大值也不是最小值的学生 SELECT DISTINCT s.student_id, s.name, c.course_name, sc.score FROM student s JOIN score sc ON s.student_id sc.student_id JOIN course c ON sc.course_id c.course_id WHERE sc.score ( SELECT MAX(score) FROM score sc2 WHERE sc2.course_id sc.course_id -- 相关子查询找当前课程最高分 ) AND sc.score ( SELECT MIN(score) FROM score sc3 WHERE sc3.course_id sc.course_id -- 相关子查询找当前课程最低分 );解析此查询为每个成绩行执行了两次相关子查询以获取当前课程的极值可能产生性能问题。优化器可能尝试将其转换为JOIN但相关性子查询限制了上拉优化。题目 4ALL/ANY 与除法运算使用 NOT EXISTS 实现-- 查询选修了计算机系全部课程的学生 SELECT s.student_id FROM student s WHERE NOT EXISTS ( -- 找出计算机系开设的、该学生没选的课程 SELECT c.course_id FROM course c WHERE c.dept 计算机系 AND NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id AND sc.course_id c.course_id ) );解析这是经典的关系除法问题的NOT EXISTS解法。它通过双重否定“不存在一门计算机系的课是他没选的”来表达全称量词。外层查询遍历学生内层第一重NOT EXISTS检查是否有一门计算机系的课不在该学生的选课列表中由最内层NOT EXISTS判断。这种嵌套相关子查询是优化器的重点优化对象在He3DB等数据库中可能会被重写为更高效的semijoin或antijoin。题目 9集合运算与性能分析-- 方法1: NOT IN (需注意NULL值问题) SELECT student_id FROM score WHERE term 2024-春 AND student_id NOT IN ( SELECT student_id FROM score WHERE term 2023-秋 ); -- 方法2: NOT EXISTS (通常更优) SELECT s.student_id FROM score s WHERE s.term 2024-春 AND NOT EXISTS ( SELECT 1 FROM score s2 WHERE s2.term 2023-秋 AND s2.student_id s.student_id ); -- 方法3: LEFT JOIN ... IS NULL SELECT DISTINCT s.student_id FROM score s LEFT JOIN score s_prev ON s.student_id s_prev.student_id AND s_prev.term 2023-秋 WHERE s.term 2024-春 AND s_prev.student_id IS NULL;性能分析NOT IN如果子查询返回结果集包含NULL值则整个NOT IN条件可能返回UNKNOWN即无结果这是逻辑陷阱。性能上数据库可能将其实现为Hash Anti Join。NOT EXISTS是表达此逻辑最清晰和安全的方式通常能利用关联字段的索引。现代数据库优化器如MySQL 8.0、He3DB擅长将NOT EXISTS重写为高效的antijoin。LEFT JOIN...IS NULL这是一种更“过程化”的写法明确指示数据库先进行连接操作。在拥有良好统计信息时优化器可能生成与NOT EXISTS相似的执行计划。但在复杂情况下它可能比NOT EXISTS产生更大的中间结果集。核心优化点数据库优化器在查询重写阶段会尝试将子查询特别是EXISTS/IN类“上拉”为JOIN以减少嵌套迭代。例如He3DB会将满足条件的EXISTS_SUBLINK转换为semijoin将NOT EXISTS转换为antijoin从而可以利用高效的连接算法如Hash Join和并行执行。能否成功上拉取决于子查询是否“相关”引用外层列以及其复杂程度。题目 10递归子查询CTEWITH RECURSIVE SubordinateTree AS ( -- 锚点直接下属 SELECT employee_id, manager_id, salary FROM employee UNION ALL -- 递归间接下属 SELECT st.employee_id, e.manager_id, e.salary FROM SubordinateTree st JOIN employee e ON st.manager_id e.employee_id ) SELECT emp.employee_id, (SELECT MAX(salary) FROM SubordinateTree st WHERE st.employee_id emp.employee_id) as max_subordinate_salary FROM employee emp;解析此CTE先递归构建出每个员工的所有下属关系包括间接。外层查询通过一个标量子查询从递归结果中找出每个员工下属的最高薪资。递归CTE是解决层次查询的强有力工具但其性能依赖于递归深度和表的索引情况。参考来源大云海山数据库(He3DB) 内核分析-子查询优化PTA沈师数据库原理——DB10_SQL实验题MySQL查询之子查询详解从执行原理到深度优化数据库子查询数据库子查询数据仓库原理与实践课程指南
