【《SQL进阶教程》】1-2 自连接的用法

二、自连接的用法：

自连接技术充分体现了SQL面向集合的特性；

一）、可重排列、排列、组合：

​ 组合分为类型：一种是 有顺序的有序对，另一种是 无顺序的无序对。

1. 使用交叉连接生成笛卡尔积就是有序对：

   SELECT P1.name AS name_1, P2.name AS name_2
     FROM Products P1, Products P2;
   

2. 有序对可以通过加WHERE过滤来去重：

   SELECT P1.name AS name_1, P2.name AS name_2
     FROM Products P1, Products P2;
   WHERE P1.name <> P2.name;
   

3. 无序对也称作组合：

   SELECT P1.name AS name_1, P2.name AS name_2
     FROM Products P1, Products P2;
   WHERE P1.name > P2.name;
   

• 实际上P1、P2来源于同一张物理表Products。但是，在SQL里，只要被赋予了不同的名称，即便是相同的表也应该当作不同的表（集合）来对待。也就是说P1和P2可以看成了碰巧存储了相同数据的两个集合。
• 使用“=”之外的比较运算符进行的连接称为 非等值连接。

二）、删除重复行：

​ 如果从物理表的层面来理解SQL语句，抽象度是非常低的。“表”“视图”这样的名称只反映了不同的存储方法，而存储方法并不会影响到SQL语句的执行和结果。无论表还是视图，本质上都是集合——集合是SQL能处理的唯一的数据结构。

三）、查找局部不一致的列：

​ 例：有一张表，列为name（姓名），family_id（家庭ID），address（地址。）需要查找在同一家庭ID，而地址不同的行。（这说明地址应该有些问题）

--使用非等值自连接
SELECT DISTINCT A1.name, A1.address
  FROM Addersses A1, Addersses A2
WHERE A1.family_id = A2.family_id
  AND A1.address <> A2.address;

四）、排序：

​ 排序一般分为两种方式，一种是跳过相同值，另一种则不跳过。

现在大多数DBMS都提供了OLAP函数来进行排序。如使用RANK函数：

SELECT name, price,
	   -- 实现跳过的排序
	   RANK() OVER (ORDER BY price DESC) AS rank_1,
	   -- 实现不跳过的排序
	   DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
  FROM products;

也可以使用非等值自连接实现：

SELECT P1.name,
	   P1.price,
	   -- 实现跳过的排序
	   (SELECT COUNT(P2.price)
	      FROM Products P2
		WHERE P2.price > P1.price) + 1 AS rank_1,
		-- 实现不跳过的排序
		(SELECT COUNT(DISTINCT P2.price)
	      FROM Products P2
		WHERE P2.price > P1.price) + 1 AS rank_2
  FROM Products P1
  ORDER BY rank_1;
	

小结：

• 与多表之间进行的普通连接相比，自连接的性能开销更大（特别是与非等值连接结合使用的时候），因此用于自连接的列推荐使用主键或者在相关列上建立索引。
• 应把表看作行的集合，用面向集合的方法来思考。
• 自连接和 GROUP BY结合使用可以生成递归集合。