SQL專欄——sql語句的基礎操作（五）嵌套子查詢

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;