针对相同的表进行的连接被称为“自连接”(self join)。一旦熟练掌握自连接技术， 我们便能快速地解决很多问题。
可重排列、排列、组合
-- 现有张表Products，内有一列name包含3个元素：苹果、橘子、香蕉
-- 生成有序对（笛卡尔乘积）
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2;
-- 答案：（苹果，苹果），（苹果，橘子），（苹果，香蕉）；
-- （橘子，苹果），（橘子，橘子），（橘子，香蕉）；
-- （香蕉，苹果），（香蕉，橘子），（香蕉，香蕉）；
-- 排除掉由相同元素构成的对
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name <> P2.name;
-- 答案：（苹果，橘子），（苹果，香蕉）；
-- （橘子，苹果），（橘子，香蕉）；
-- （香蕉，苹果），（香蕉，橘子）；
-- 用于获取组合的 SQL 语句
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name > P2.name;
-- 答案：（苹果，橘子）；
-- （香蕉，橘子）；
-- （香蕉，苹果）；
-- 原理：这条 SQL 语句所做的是，按字符顺序排列各商品，只与“字符顺序比自己靠前”的商品进行配对。
-- 到这里，我们终于得到了无序对。
-- 想要获取 3 个以上元素的组合时，像下面这样简单地扩展一下就可以
SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3
FROM Products P1, Products P2, Products P3
WHERE P1.name > P2.name AND P2.name > P3.name;
-- 答案：（香蕉，苹果，橘子）；
- 如这道例题所示，使用等号“=”以外的比较运算符，如“<、>、<>” 进行的连接称为“非等值连接”。这里将非等值连接与自连接结合使用了，因此称为“非等值自连接”。
删除重复行
重复行有多少行都没有关系。通常，如果重复的列里不包含主键，就可以用主键来处理，但像这道例题一样所有的列都重复的情况，则需要使用由数据库独自实现的行 ID。这里的行 ID 可以理解成拥有“任何表都可以使用的主键”这种特征的虚拟列。
-- 用于删除重复行的 SQL 语句 (1) :使用极值函数
-- 在下面的 SQL 语句里，我们使用的是 Oracle 数据库里的 rowid
delete from products p1
where rowid < (select max(p2.rowid)
from products p2
where p1.name = p2.name and p1.price = p2.price);
-- 用于删除重复行的 SQL 语句 (2) :使用非等值连接
DELETE FROM Products P1
WHERE EXISTS ( SELECT *
FROM Products P2
WHERE P1.name = P2.name
AND P1.price = P2.price
AND P1.rowid < P2.rowid )
查找局部不一致的列
-- 用于查找是同一家人但住址却不同的记录的 SQL 语句
SELECT DISTINCT A1.name, A1.address
FROM Addresses A1, Addresses A2
WHERE A1.family_id = A2.family_id
AND A1.address <> A2.address ;
排序
-- 排序 :使用窗口函数
-- 在出现相同位次后，rank_1 跳过了之后的位次，rank_2 没有跳过， 而是连续排序。
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
FROM Products P1 ORDER BY rank_1;
本节要点
- 自连接经常和非等值连接结合起来使用。
- 自连接和GROUP BY结合使用可以生成递归集合。
- 将自连接看作不同表之间的连接更容易理解。
- 应把表看作行的集合，用面向集合的方法来思考。
- 自连接的性能开销更大，应尽量给用于连接的列建立索引。
