一、最终正确结果
1 ,问题:检索"01"课程分数小于60,按分数降序排列的学生信息
2,表格:见Mysql的select多表查询嵌套二
3,代码:
SELECT student.*,score.s_score from student ,score WHERE student.s_id=score.s_id and
score.c_id="01" AND score.s_score<60 ORDER BY score.s_score DESC
4,结果:
二、问题分析
1,代码:
SELECT student.* from student ,score WHERE student.s_id in(SELECT s_id from score
WHERE score.c_id="01" AND score.s_score<60) ORDER BY score.s_score DESC
2,结果:(共36条记录)
3,代码:
SELECT s_id from score WHERE
score.c_id="01" AND score.s_score<60
4,结果:(共2条记录)
5,代码:
SELECT score.s_score from score ORDER BY score.s_score DESC
6,结果:(共18条记录)
由以上6条可以看出,select嵌套时,由于外层查询有两张表中的内容字段组成,其中学生信息表有2条满足,成绩表有18条数据,故共计以36条数据显示所查找的信息。显然这不是我们要的格式。另外分数也不是按照条件select查询出来的结果开始降序,故内层select的s_id只适用于学生表。
7,代码:
SELECT student.* ,score.s_score from student ,score WHERE student.s_id in(SELECT s_id from score where score.c_id="01" AND score.s_score<60) and score.c_id="01" AND score.s_score<60 ORDER BY score.s_score DESC
8,结果:
补充and条件语句。由8结果可验证上面结论
三,结论
一般出现查询结果重复循环出现,都是由select多表查询时,条件补充不全引起的。一般可以通过像一那样简化连接来解决问题。