SQL多表查询

搬出问题背景：

每一位同学在期末放假的时候，都想知道自己各门课程的考试成绩（当然，或许也有不想知道的！：））。我们来看看如何满足同学们的需求。

先来分析一下，当同学们查成绩的时候，需要呈现出什么样的内容。大概会有：学生姓名、学号、课程名称、成绩。大抵如此。可以很明确地发现，这些信息分布在上述三张表中，所以这三张表都是我们的查询目标。那这样的SQL怎么写呢？是这样吗：

select Sno,Sname,Cname,Grade from Student,Course,SC

这条语句如果在数据库中被执行？会得到什么呢？

结果是：会得到一堆乱七八槽的东西！结果中的记录数量=Student的记录数*Course的记录数*SC的记录数。咱们想要的应该不是这样？因为至少从记录数上看，结果是错误的，它不应该超过学生选课总数（即SC表的记录数量）。哪里出了问题？善于联想的同学或许已经从上述描述中想到了一种运算，因为这种运算的结果正好是上面这个结果。什么运算？

笛卡尔积！

没错！就是它！上面的SQL语句执行的结果正是做的笛卡尔积。但这种运算并不能给予我们正确的结果。我们需要做的就是增加表和表之间的连接条件，通过连接条件来告诉这些表，哪些记录可以匹配、合并，而不是漫无目的地将任意两条记录随意地合并在一起。

那么连接条件应该怎么写呢？让我们来考察一下表Student和表SC。大家肯定已经明确了，这两张表之间是有关联的。即SC中的Sno参考了Student中的Sno；换句话说，Student中的Sno是外码，SC表中的Sno来自于Student中的Sno。所以对于Student中的某一名同学，如果SC表中某些记录的Sno等于这个同学的Sno，那么我们就可以说这些记录就是这个同学的选课记录。因此Sno是我们关联这两张的关键。

好了，我们现在应该可以写出这两张表之间的连接条件了：

Student.Sno=SC.Sno

好，但是我们还有一张表：Course。道理和上面相同，我们也得把它拉帮入伙！谁去拉？Student?SC?Student是无法完成这个任务的，因为它没有可以与Course相关联的字段；但是SC表中有这个东西，这就是Cno。所以我们可以凭借Cno，通过SC去连接Course。再写一个连接条件：

SC.Cno=Course.Cno

每一个连接条件都连接了两张表呢，我们把它们并起来就实现了对三张表的连接：

Student.Sno=SC.Sno and SC.Cno=Course.Cno

最后来完善我们最开始给出的SQL语句：

select Sno,Sname,Cname,Grade 

from Student,Course,SC

where Student.Sno=SC.Sno and SC.Cno=Course.Cno

这条语句基本完成，但如果直接放到SQLServer中去执行，依然会给我们报错。报错的信息大概是：Sno不明确。什么地方的Sno不明确？为什么不明确呢？让我来回答一下这两个问题：

1、Select后的Sno不明确；

2、因为在Student和SC表中都存在Sno字段，因此select后的Sno到底是谁的Sno呢？好，报的不明确就是指这个原因。

既然如此，那我们怎么让它明确呢？加上表名限定就可以了。如下：

select Student.Sno,Sname,Cname,Grade 

from Student,Course,SC

where Student.Sno=SC.Sno and SC.Cno=Course.Cno

大家会想另外一个问题吗？Sname、Cname、Grade为什么不加？因为这三个字段都只是在各自的表中出现，并不会引起混淆的情况，所以它们可加，也可不加表名限定。这个由你决定。

OK！这就是我们最终的SQL语句，它完成了对Student、SC、Course三张表的连接。这条查询语句的执行结果可以告诉我们每一名同学的课程成绩。

闲话一下：其实通常由于表名较长，且难写，因此出于避免出错和简洁代码的考虑，我们在进行多表连接时，都会给表名一个更为简洁的别名，比如上面的语句可写成：

select s.Sno,Sname,Cname,Grade

from Student s,Course c,SC sc

where s.Sno=sc.Sno and sc.Cno=c.Cno

看上去是不是要好一点...

通过以上稍显啰嗦的讨论，大家应该明白了多表连接的基本原理和方法。最后总结一下:

1、什么时候需要多表连接？

当我们查询的字段和条件字段分布在多张表的时候，就需要做多表连接。

强调：多表连接是很耗费数据库资源的，除非必要，否则不要频繁地做多表连接。单表可以完成查询的，一定不要做多表连接查询。

2、多表如何连接？

找出表之间的关联字段，通过对关联字段的比较来完成表与表之间的连接。这种比较不一定是等值比较；这种关联字段也不一定是外键字段。

提醒：如果两张表无法做直接的连接，那么就必须要想方设法地寻找第3张表来间接地关联两表，也就是要为无法直接关联的两张表找一个桥梁。

【例1】查询选修了“数据库”课程的学生姓名。

select Sname

from Course c, SC sc, Student s

where sc.cno=c.cno and sc.Sno=s.Sno and c.Cname=‘数据库’

多表连接——自连接

让我们来实现这样一个查询需求：

查询年龄大于'王五'的年龄的学生姓名。

什么意思呢？就是要找出那些年龄比王五大的学生的姓名。我们用SQL翻译一下：

select Sname from Student where Sage>王五的年龄

王五的年龄是什么？不知道呀，这个数据在Student表中，也得通过一条SQL语句得到：

select Sage from Student where Sname='王五'

怎么办？这条语句怎么写？有点摸不着头脑的感觉吗？让我们换一个思路。我现在将Student复印一份给你。好了，算上原来的Student和现在复印的Student，那么你就有两张Student表了。我们暂且将这两张表称作：s1和s2。那么事情就变得简单多了，不就是一个两表查询码：

select s1.Sname from s1,s2 where s1.Sage>s2.Sage and s2.Sname='王五'

是这意思吧？！

现在的问题是：什么是复印1张表？怎么复印1张表？

这个复印技术并不是一个新技术，是一个大家都熟悉得不能再熟悉的一种技巧：别名运算。也就是说我可以给表起1个别名、2个别名，...，甚至更多。每起1个别名，就可以近似地理解为创建了一个原表的镜像，也就是我们说的复印了1份这张表。但是需要说明的是，这种表的增加只是你的一种幻觉，其实表一直都只有1份。只是对于我们来说，通过别名运算，好像是增加了表：s1,s2,s3,...。幻觉就幻觉吧，我看到了这么多张表，可以解决问题了，这就够了！

所以上面的SQL语句就可以写成：

select s1.Sname

from Student s1,Student s2

where s1.Sage>s2.Sage and s2.Sname='王五'

这就是我们最终的SQL语句。

分析一下：这条SQL语句是一个多表连接查询，进行连接的目标表有两张，但都是同一张表：Student。我们就把这种自己和自己连接的查询叫做自身连接查询。

提醒：我们在介绍多表查询的时候说到了：连接不一定是等值比较，也不是一定非要通过外键关联。这里这个例子就很好地说明了这一点。Sage并不是外键，同时两张表的Sage所做的比较也并不是等值比较。

最后，让我们举个例子来结束本部分内容：

【例1】查询成绩高于学号为3的学生选修的课程号为5的成绩的所有学生选课记录，并按成绩升序排列。

select sc2.*

from SC sc1, SC sc2

where sc2.Grade>s1.Grade and sc1.Sno=3 and sc1.Cno=5

order by sc2.Grade 

解题要点：把SC通过别名运算变成两张表，一张表(sc1)去搜索3号学生所选的6号课程的课程成绩，另一张表(sc2)对所有学生的选课数据进行扫描，逐一取出成绩去和sc1的查询结果进行比较，从而找出符合条件的学生。