SQL中关于自身连接的一些体会

自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据

本例列出购买了相同产品的所有顾客
第一步：建立一个表Sales

CREATE TABLE sales
(     buyer_id int
      ,prod_id  varchar(10)
      ,qty       money
)

 

第二步：往表sales中插入一些字段 其中商品号为2的有两个顾客进行购买

INSERT INTO sales
SELECT 1,2,15
UNION all
SELECT 1,3,5
UNION all
SELECT 4,1,37
UNION all
SELECT 3,5,11
UNION all
SELECT 4,2,1006

 

第三步：选出所以表中数据进行察看
SELECT * FROM sales

 

 

第四步：求取购买了相同产品的所有顾客

SELECT a.buyer_id AS buyer1,a.prod_id,b.buyer_id AS buyer2
FROM     sales AS  a
JOIN       sales AS  b
ON          a.prod_id= b.prod_id
WHERE a.buyer_id > b.buyer_id

 

 

 

总结：上述步骤及其SQL代码虽然能够满足要求，但是我们的主要目的是

一：如何使用自身连接

答：使用别名 来代替

二：如何删除重复行

答: 使用比较运算符 〉

    再次假设我们不使用比较运算符〉 而使用<> 结果会如何呢 请看

SELECT  a.buyer_id AS buyer1,a.prod_id,b.buyer_id AS buyer2
FROM     sales AS  a
JOIN       sales AS  b
ON           a.prod_id= b.prod_id
WHERE a.buyer_id <> b.buyer_id

结果如下：

 

 

 

 

这样的话看上图：会有重复现象 即2号商品会被顾客4或则顾客1重复购买一次

但是数据库中却该商品却只是购买了一次。所以运用大于号可以排除该情况

 

当创建自链接时可使用WHERE子句删除重复行 注意：当ｗｈｅｒｅ子句中使用了大于号操作符时

所以重复的行都被删除了

 

三：我们的重点其实到这里已经实现了 但是该SQL在现实中却实实在在存在一些问题。

  例如：如果一个商品被三个顾客同时购买了的话 那么上述SQL就不适用了，自己研究了N种方法，如使用行列转换算法。但是都不大可行 唯一能够想到就是在Select 行列中多

写一个buyer_id AS buyer3  在连接中多加一个表 Join sales AS c 相应条件也要进行添加