My SQL 子查询的三种方法

#查询平均分最高的科目名称以及所属院校与总人数
#子查询
SELECT courseId FROM score group by courseId
order by avg(result) desc limit 1;
#父子联合查询
select courseName '科目名称',department'所属院系',
number '课程总人数' from course where id=(
SELECT courseId FROM score group by courseId
order by avg(result) desc limit 1
);

#找朱重八创建之后的所有人信息
select createDate from student where userName='朱重八';
#日期的子查询结果
select * from student where createDate>(
select createDate from student where userName='朱重八'
);

#平均分低于75的所在科目名称
select courseId,avg(result) from score group by courseId having avg(result)>75;
#多个返回结果的子查询方法
select * from course where id not in (
select courseId from score group by courseId having avg(result)>75
);