需要有一定的MySQL基础才可以看哦~
有学生成绩表(sc)如下,三个字段分别是:s_id 学生编号,c_id 课程编号,score 考试分数
问题一:求每个课程的第二名和第三名的成绩信息;
大家可以停下来想一下,要怎么才能实现?
求每个课程的第二名和第三名,如何判断某个成绩是第二名或者第三名呢?可以看比这个成绩高的有几个,如果有1个或者2个,那这个成绩一定是第二名或者第三名,如果不是1或者2,那就不是题目想要的,这也是这道题的解题思路。
在解这道题之前,需要先了解下mysql查询语句的运行逻辑,如下查询语句:select * from sc where score>=60;where后面跟的是条件score>=60,所以得到的结果是成绩大于等于60的记录,那where后面的score是什么?很多人会认为score就是sc表的字段,这个思路是错的,这里面有一个遍历的思想,from语句会遍历查询表的每一条记录,再将这条记录放到where语句进行判断,如果得到的是true,就返回这条记录,如果得到的是false,就过滤这条记录,所以where后面的score不是一个字段,而是一个具体的值,当from遍历第一条记录是,score是80,遍历第二条记录时,score是90...
上面说的遍历思想要理解好才能做这道题,这道题的完整代码如下:
select * from sc as a
where (select count(*) from sc where c_id=a.c_id and score>a.score) in (1,2);
代码是在where语句用了子查询,首先外层查询是查询sc表,并给了别名a,遍历a表的每一条记录,遍历第一条数据是,a.s_id为01,a.c_id为01,a.score为80,将这条记录放到where语句后面的子查询:(select count(*) from sc where c_id=a.c_id and score>a.score),子查询也是查询sc表,对满足条件的记录进行计数,条件是c_id=a.c_id and score>a.score ,这里的a.c_id和a.score是外层查询的第一条记录,也就是a.c_id为01,a.score为80,可以等价于(select count(*) from sc where c_id=“01" and score>80),求的是课程编号为01,分数大于80的人有几个,如果=1或者=2,满足条件返回true,否则返回false。然后外层查询遍历第二条记录,遍历第三条记录,依次遍历到最后一条记录,把每一条记录的值放到where的子查询里,满足条件(每个课程的第二名或者第三名)的返回,不满足条件的过滤。
为什么要给外层查询别名,因为如果不给别名,就会和子查询的sc重复,造成语句逻辑混乱,不能执行。当然也可以给子查询别名,外层查询不给别名,这样也是可以的。
如有不懂的,或者有更好的方法,欢迎留言交流~