【MySql】相关子查询

一、相关子查询

基本特点：执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。
执行过程：
（1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。
（2）执行内层查询，得到子查询操作的值。
（3）外查询根据子查询返回的结果或结果集得到满足条件的行。
（4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。
因此，相关子查询时一个类似于循环的过程。

二、案例

有一张score表如下：

问题：查询大于每门课程平均分的数据

-- 主语句
select * from world where 成绩>分科平均分；
-- 子查询：分科平均分
#方法1.子查询用having子句通过条件筛选这个group by 数组后，就能使得每一行的数据对应一个avg(成绩)
select * ,
(select avg(成绩) from score as s2 group by 课程号 having s1.课程号=s2.课程号 ) as 分科平均成绩 
from score as s1;

#方法2.这里的where子句可以起到和having一样的效果，where已经起到选组作用，group by 子句可以省略
select * ,
(select avg(成绩) from score as s2 where s1.课程号=s2.课程号 /*group by 课程号*/) as 分科平均成绩 
from score as s1;

--将大于分科平均成绩的数据查询出来
#方法1
select * from score as s1 
where 成绩>(select avg(成绩) 
from score as s2 
group by 课程号 having s1.课程号=s2.课程号 );

#方法2
select * from score as s1 
where 成绩>(select avg(成绩) from score as s2 where s1.课程号=s2.课程号 );

总结：
相关子查询用于表内查询
连接内外两张表的where条件相当于分组条件
表里面有几条数据，子查询就执行几次

三、练习

1、查询每门课程最高分，并且显示对应的学号
思路:如果只要查询课程最高分，只需要group by即可，但是学号是没有关联意义的

-- 主语句
select * from score where 成绩 = (同一课程下的所有成绩项)的最大值

思路一：

成绩 = (同一课程下的所有成绩项)的最大值可用用max函数分组计算：
成绩 = select max(成绩) from score 根据课程号分组
如果不用相关子查询，子查询直接回得到一个数组，无法进行等号运算，会报错。

select * from score s1 
where 成绩=(select max(成绩) from score group by 课程号); 

因此需要用到外表，用类似循环的方式将成绩逐一比较

select * from score s1 
where 成绩=(select max(成绩) from score s2 where s1.课程号=s2.课程号); 

思路二：
成绩 = (同一课程下的所有分数项)的最大值可以翻译为：
成绩 >= all(同一课程下的成绩项)
同一课程下的成绩项用相关子查询表示
select 成绩 from 内表 where 内表.课程号=外表.课程号

select * from score s1 
where 成绩>=all (select 成绩 from score s2 where s1.`课程号`=s2.课程号);

2.抽取每个学号下课程号最小的一门课的数据

select * from score 
where 课程号=(同一学号下的所有课程号数)的最小值

课程号=(同一学号下的所有课程号数)的最小值可以翻译为：
课程号<=all(同一学号下的课程号数)
同一学号下的课程号数用相关子查询表示
select 课程号 from 内表 where 内表.学号=外表.学号

select * from score s1 
where 课程号<=all (select 课程号 from score s2 where s1.学号=s2.学);

3.查询出所有学生分数都>=80的科目

select 课程号 from score 
where 80<=同一科目下的所有成绩项目 group by 课程号;

同一科目下的所有成绩项目>=80可以翻译为：
80<=all(同一科目下的所有成绩)
同一科目下的所有成绩用相关子查询表示
select 成绩 from 内表 where 内表.课程号=外表.课程号

select 课程号 from score s1 
where 80<=all(select  成绩 from score s2 where s1.课程号=s2.课程号) 
group by 课程号;

注意：运算符要写在左边

4.查询出比同科目下其他分数都高出10分及以上的所有数据

select * from score 
where 成绩-10>=同一科目下的其他成绩项目;

成绩-10>=同一科目下的其他成绩项目可以翻译为：
成绩-10>=all(同一科目下的其他成绩)
同一科目下的其他成绩用相关子查询表示
select 成绩 from 内表 where 内表.课程号=外表.课程号 and 内表.学号=外表.学号

select * from score s1 
where 成绩-10>=all(select 成绩 from score s2 where s1.课程号=s2.课程号 and s1.学号<>s2.学号);

注意;成绩-10>=同一科目下的其他成绩项目,必须是其他成绩，因为成绩-10是不可能>=本身的，所以一定要将本身排除，而在同一课程号下，学号是唯一标志。