sql 自连接

摘要：本文对SQL语言中的自身连接进行了详细的论述，讨论了自身连接存在的必要性，并通过实例对SQL中自身连接查询进行了深入的剖析。
　　关键词：SQL 自连接 连接查询
　　
　　一、连接查询
　　
　　在SQL中，如果一个查询同时涉及到两个以上的表，那么这种查询就称为连接查询。连接有多种形式，有等值连接、自然连接、非等值连接、外连接、复合条件连接和自身连接等。
　　大多数的连接，都是涉及到不同的表，以下面的SQL代码为例：
　　SELECT Student.SNumber， Student.SName， StuCour.*
　　FROM Student， StuCour
　　WHERE Student.SNumber=StuCour.SNumber；
　　在上面的SQL代码中，有两个表：学生信息表Student（SNumber， SName， SSex， SAge）和学生选课信息表StuCour（SNumber， CNumber， Grade），这两个表都有一个代表学生学号的叫做SNumber的属性。查询结果是将Student表和StuCour表中SNumber相同的学生的学号、姓名和选课信息显示出来。
　　这种连接查询，使用多个表格，并利用了不同表格中相应的列的逻辑关系，SQL代码简单、清楚、明了。但连接操作，并不只存在于多个表之中，有时，对单表进行查询操作时，也必须使用连接操作。
　　
　　二、用SQL自连接查询处理列之间的关系
　　
　　SQL自身连接，可以解决很多问题。下面举的一个例子，就是使用了SQL自身连接，它解决了列与列之间的逻辑关系问题，准确的讲是列与列之间的层次关系。SQL代码如下：
　　SELECT FIRST.CNumber， SECOND.PCNumber
　　FROM Course FIRST， Course SECOND
　　WHERE FIRST.PCNumber=SECOND.CNumber；
　　在这个代码中，只涉及到一个表，即课程信息表COURSE(CNumber， CName， PCNumber)，其中CNumber是该课程的课程号，PCNumber是该课程的先修课课程号。在FROM子句中，为Course表起了两个不同的别名，即FIRST和SECOND，即为Course表创建了两个不同的实例。查询时，使用了条件语句WHERE，要求FIRST表中的先修课号PCNumber同SECOND表中的课程号CNumber相同，而查询所需要的结果，是FIRST表的课程号CNumber和SECOND表中的PCNumber，那么查询结果，就应该是FIRST表中的课程号CNumber和该课程号所对应的间接先修课课程号。
　　
　　同样的查询，如果不使用自连接，而纯粹是对单表本身进行操作，那将是非常困难的。假设在这个表中，有两个元组（cnumber1， cname1， pcnumber1）和（cnumber2， cname2， pcnumber2），其中，pcnumber1=cnumber2。在同一个COURSE表上，关于课程号的信息，只有CNumber和PCNumber这两个属性，它们只能表示一个课程号(cnumber1)和该课程号直接先修课(pcnumber1=cnumber2)的一层关系。而间接先修课，需要求出cnumber1和pcnumber2的关系，这是一个二层关系。对于这个只能表示一层关系的表而言，如果纯粹对单表进行操作而不使用自然连接，那么一个可行的方案是，为Course表再增加一个列PPCNumber，令该列表示间接先修课，那么，通过三个列，就可以表示这种二层关系了。
　　通过这个例子以及对这个例子的分析，我们可以初步得出结论，自连接查询，可以表示表中各列的层次关系。当所要查询的信息都出于同一个表，而又不能直接通过该表的各个列的直接层次关系得到最终结果的时候，那么应该考虑使用表的自连接查询。
　　
　　三、SQL自连接查询表示其它关系
　　
　　除了处理前面例子中的列之间的层次关系之外，SQL自连接查询还可用于处理列之间的顺序关系、因果关系等多种逻辑关系。此外，SQL自身查询还可以用于处理单列本身的逻辑关系。下面的例子，就说明了对单列的逻辑关系的处理。
　　SELECT FIRST.Num， FIRST Stop， SECOND.Stop
　　FROM Route FIRST， Route SECOND
　　WHERE FIRST.NUM=SECOND.NUM；
　　这个代码中，只涉及到一个表Route（Num， Stop），这个表可以表示某一线路的火车的车站线路信息。Num表示该车的车次号，Stop表示该次车停靠的城市名称。上面的代码，可以求出某一线路的火车可以联通的任意两个城市的名称。
　　
　　在这里例子中，连接的操作对象只有Num这一个列，通过相同的车次号，找出该列车联通的任意两个城市的信息。在这个例子中，原来表Route中的每一个元组，只能表示车号和该车的某一站点的信息，实际上，这是“1Vs1”的映射关系。如果要表示两个站点的联通关系，那么就应该把两个“1Vs1”关系合并，形成“1Vs多”的关系。我们利用自连接，很容易地解决了这个关系扩充的问题。
　　下面的这里例子，是对单一的列进行连接处理：
　　SELECT FIRST.Num， SECOND.Num， FIRST.Stop
　　FROM Route FIRST， Route SECOND
　　WHERE FRIST.Stop=SECOND.Stop；
　　
　　上面的SQL代码，求出了路经相同城市的车次的信息。原表中的车次和车站是“1Vs1”关系，通过自连接后，得到了车次和车站的“多Vs1”关系。
　　
　　总结
　　
　　同其它连接相比，SQL自连接查询本身并没有什么特殊的。但是，在应用中，自连接查询因为其语法结构简单，而逻辑结构复杂，语义往往不是那么容易被人理解，因此，在使用时，经常令人觉得迷惑不解。但只要把自连接运用的得当，把单表看成是多表，牢固树立这一思维定式，我们会发现，自连接查询会为我们解决很多复杂的问题。
=================================================================

 

到目前为止，我们连接的都是两张不同的表，那么能不能对一张表进行自我连接呢？答案是肯定的。
有没有必要对一张表进行自我连接呢？答案也是肯定的。

表的别名：
一张表可以自我连接。进行自连接时我们需要一个机制来区分一个表的两个实例。
在FROM clause（子句）中我们可以给这个表取不同的别名， 然后在语句的其它需要使用到该别名的地方
用dot（点）来连接该别名和字段名。

我们在这里同样给出两个表来对自连接进行解释。
爱丁堡公交线路，

车站表：
stops(id, name)

公交线路表：
route(num, company, pos, stop)

关于这两个表更详细的解释可以参考这里：http://sqlzoo.cn/buses.htm

一、对公交线路表route进行自连接。

SELECT * FROM route R1, route R2
  WHERE R1.num=R2.num AND R1.company=R2.company

    我们route表用字段(num, company)来进行自连接． 结果是什么意思呢？
你可以知道每条公交线路的任意两个可联通的车站。

二、用stop字段来对route（公交线路表）进行自连接。

SELECT * FROM route R1, route R2
  WHERE R1.stop=R2.stop;

查询的结果就是共用同一车站的所有公交线。这个结果对换乘是不是很有意义呢。

    从这两个例子我们可以看出，自连接的语法结构很简单，但语意结果往往不是
那么容易理解。就我们这里所列出的两个表，如果运用得当，能解决很多实际问题，
例如，任意两个站点之间如何换乘。

SELECT R1.company, R1.num
  FROM route R1, route R2, stops S1, stops S2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=S1.id AND R2.stop=S2.id
    AND S1.name='Craiglockhart'
    AND S2.name='Tollcross'