【SQL】使用SQL查询各个课程的最高分、第二高分、最高分人数

现有3个表，分别为student(sid, name)，course(cid, name)，student_course(sid, cid, score)。

要求查询各个课程的最高分、第二高分、最高分人数，查询的结果有5个属性，分别为cid, name, max_score, max_score2, max_score_count。此题查询代码如下。

select cid,name,
    (select max(t1.score) from student_course t1 
     where t1.cid =t0.cid)max_score,
    (select max(t2.score) from student_course t2 
     where t0.cid =t2.cid and score not in (select max(t1.score) from student_course t1 where t1.cid =t0.cid))max_score2,
    (select count(distinct sid) from student_course t1 
     where t1.cid =t0.cid and t1.score = 
     (select max(t1.score) from student_course t1 where t1.cid =t0.cid)) max_score_count
     from course t0

这种思想主要为，select查找的每一个属性都是一个单独的关系，因此将嵌套子查询用在select中。
最外层的查询为

select cid, name from course t0

这也是最核心的约束，即查询结果的cid和name先说好在course里找，用t0来表示最外层查询的表，并进一步用来约束内部的查询。

举个例子，在下列子查询中：

 (select max(t1.score) from student_course t1 
     where t1.cid =t0.cid) max_score,

我们考虑的是在student_course(令别名为t1)中查找最高分，如何定义t1查询结果与t0中cid、name的一一对应关系呢？只需令t1.cid =t0.cid即可。最后再将整个查询的结果定为max_score，作为select的一个属性，即可得到最高分。

同理，查询第二高分，只需查询“不在最高分中的最高分”即可，不再赘述。

而查询最高分人数时，思想是“在student_course表中查询每一门课对应的max(score)的人数count(cid)”，当然，这个子查询也只需要用’t1.cid = t0.cid’约束即可把它与外层查询的cid、name一一对应起来。