六、SQL–表连接⑤(自连接)

自连接：参与连接的表为同一张表，即与自身连接。
交叉连接、内连接、外连接等连接方式中只要参与连接的表是同一张表，那么它们就可以被称为自连接。

自连接主要用途：就是检索一张表内部的匹配情况
如：检索与另外一个订单的订单类型一样的所有订单的列表。可为以下SQL语句：

SELECT FNumber,FPrice,FTypeId FROM T_Order WHERE FTypeId= FTypeId

执行结果：

FNumber FPrice FTypeId
K001 100.00 1
K002 200.00 1
T003 300.00 2
N002 100.00 2
N003 500.00 4
T001 300.00 3
T002 100.00 1

这里显示出了T_Order表中的所有数据，而不是想像中的结果。因为这里的WHERE语句条件永远为真，相同行的相同列总是等于它自己，因此结果集中包含了表中的所有记录。

如果要实现要求的功能，可以假象存在另外一个与T_Order表完全相同的表，这样我们就可以在这两个表之间进行任意的连接操作了。我们尝试套用INNER JOIN的写法，只是将参与连接的两个表名都设置为T_Order，SQL语句如下：

SELECT FNumber,FPrice,FTypeId FROM T_Order INNER JOIN T_Order ON T_Order.FTypeId=T_Order.FTypeId

执行以后数据库系统会报出如下的错误信息：

FROM 子句中的对象"T_Order" 和"T_Order" 具有相同的表现名称。请使用相关名称来区分它们。

很显然，因为这里两次使用了T_Order表，但是数据库系统无法区分这两个T_Order表，因此必须为它们指定不同的别名，修改后的SQL语句如下：

SELECT o1.FNumber,o1.FPrice,o1.FTypeId,o2.FNumber,o2.FPrice,o2.FTypeId FROM T_Order o1 INNER JOIN T_Order o2 ON o1.FTypeId=o2.FTypeId

这里为T_Order表取了两个别名o1和o2，并且在引用表中列的时候也明确的指定了列属于那个表下的，这样数据库系统就能区分这两个别名代表的表了。使用别名以后我们可以将这两个别名看作结构相同、数据相同的两个不同的表，这样就可以避免思维上的障碍。

这个SQL语句执行成功，没有语法错误，它是一个有效的自连接，不过它执行所产生的结果却不是正确的。比如第一行中“订单号为K001的订单与订单号为K001的订单的订单类型相同”，自己的订单类型当然与自己相同，这当然是正确的，可是这样的结果对我们来说是没有意义的。ON子句中指定两个表的FTypeId字段必须相同，当然对于同一个订单来说，它们肯定是相同的，而这里真正要查询的是具有相同的FTypeId字段值的两个不同的订单，因此需要在连接条件中添加一个新的条件，修改后的SQL语句如下：

SELECT o1.FNumber,o1.FPrice,
o1.FTypeId,o2.FNumber,o2.FPrice,o2.FTypeId
 FROM T_Order o1 INNER JOIN T_Order o2
 ON o1.FTypeId=o2.FTypeId and o1.FId<>o2.FId

ON子句末端添加的新条件“and o1.FId<>o2.FId”检查了别名为o1的表的主键不等于名为o2的表的主键，因为主键是唯一的，所以这样就可以确保得到的是一个不同的订单，从而不包含同一张订单。

执行结果：

FNUMBER FPRICE FTYPEID FNUMBER FPRICE FTYPEID
T002 100 1 K001 100 1
K002 200 1 K001 100 1
T002 100 1 K002 200 1
K001 100 1 K002 200 1
N002 100 2 T003 300 2
T003 300 2 N002 100 2
K002 200 1 T002 100 1
K001 100 1 T002 100 1

可以看到执行结果中已经去掉了相同订单的匹配，但是仔细观察仍然会发现存在重复的行。比如第一行的最后一行。o1表中的T002订单与o2表中的K001订单匹配，然后o2表中的K001订单与o1表中的T002订单匹配，也就是说数据库系统把“A匹配B”与“B匹配A”看成了两个不同的匹配，而实质上它们只是方向不同的相同的匹配，因此需要防止出现这样相同的匹配结果。因为出现上面这种问题的原因是因为存在“A匹配B”与“B匹配A”这两个方向的匹配，那么我们只要破坏这种双向匹配就可以了，最简单的方式就是要求o1的表的主键值于o2的表的主键值。修改后的SQL语句如下：

SELECT o1.FNumber,o1.FPrice,o1.FTypeId,o2.FNumber,o2.FPrice,o2.FTypeId FROM T_Order o1 INNER JOIN T_Order o2 ON o1.FTypeId=o2.FTypeId and o1.FId<o2.FId

这里仅有的改变是ON子句中连接条件的最后面部分，其原来的形式是：
o1.FId<>o2.FId
这个ON子句仅仅应用于两个表中FId字段值不同的记录。只要o1表与o2表中的FId字段值不同，则记录就会被包含字结果集中，因此将导致重复，所以这里将ON子句的这个SQL片段替换为：
o1.FId<o2.FId
现在o1表的一个记录行仅仅在它的FId字段值小于o2表的一个记录行仅仅在它的FId字段值的时候，才出现在结果集中。这确保了一行数据仅出现在结果集中一次。

执行结果：

FNUMBER FPRICE FTYPEID FNUMBER FPRICE FTYPEID
K001 100 1 K002 200 1
T003 300 2 N002 100 2
K002 200 1 T002 100 1
K001 100 1 T002 100 1