SQL专栏——sql语句的基础操作(五)嵌套子查询
原创
©著作权归作者所有:来自51CTO博客作者wx59658a994b151的原创作品,请联系作者获取转载授权,否则将追究法律责任
grade表
result表
subject表
ORDER BY
题目要求;
select r.studentresult
from result r
INNER JOIN subject s
on r.subjectno=s.subjectno
where s.subjectname='数据库结构-1'
Order by r.studentresult desc limit 10;
LIMIT
select s.studentno,
from student s
INNER JOIN subject s
on r.subjectno=s.subjectno
where s.subjectname='数据库结构-2'
Order by r.studentresult desc limit 5;
select s.studentno,s.studentname,r.studentresult
from student s
join result r
on s.studentno=r.studentno
join subject sub
on sub.subjectno=r.subjectno
where sub.subjectname='JAVA第一学年'
AND r.studentresult>=80
ORDER BY r.studentresult desc limit 0,10;
子查询
mysql> select studentno,studentname
-> from student where studentno
-> in(select studentno from result where subjectno=(select subjectno from subject where subjectname='高等数学-2' and studentresult>=80));
案例
select s.studentno,s.studentname,r.studentresult
from student s
join result r
on (s.studentno=r.studentno)
where r.subjectno=(select subjectno from subject where subjectname='C语言-1')
ORDER BY r.studentresult desc limit 0,5;
select s.subjectname,max(r.studentresult),min(r.studentresult),avg(r.studentresult) avg_score
from subject s
join result r
on s.subjectno=r.subjectno
group by s.subjectname
having avg_score>60;