【sql： 练习题12】检索" 01 "课程分数小于 60，按分数降序排列的学生信息

题目：检索" 01 "课程分数小于 60，按分数降序排列的学生信息

分析：按分数降序排列 应该是要用 order by 关键字

降序： desc

升序：asc

 

我以前的写的想法是： 先查处01 "课程分数小于 60 然后 order by  分数 desc 再去关联student 表

SELECT student.*,r.score FROM student,
     (SELECT * FROM student_score WHERE student_score.courseid = 01 AND student_score.score< 60 ORDER BY score DESC)r
WHERE student.id = r.studentid;

其实这样先排序 再去查student 表，查出的学生并不是按照分数降序排序的 没有达到效果

 

优化：先不排序，查出来学生再排序

 

SELECT student.*,r.score FROM student,
     (SELECT * FROM student_score WHERE student_score.courseid = 01 AND student_score.score< 60)r
WHERE student.id = r.studentid ORDER BY r.score DESC;

 

 

 

再次优化：可以多表查询

 

 

SELECT student.*,score FROM student,student_score WHERE
   student.id = student_score.studentid AND student_score.courseid = 01 AND student_score.score < 60 ORDER BY score DESC