【SQL进阶教程】自连接的用法

与多表之间进行的普通连接相比，自连接的性能开销更大（特别是与非等值连接结合使用的时候），因此用于自连接的列推荐使用主键或者在相关列上建立索引。

可重排列、排列、组合

假设有一张存放了商品名称及价格的表，表里有“苹果、橘子、香蕉”这3条记录。在生成用于查询销售额的报表等的时候，有时会需要获取这些商品的组合。

用SQL生成有序对非常简单。像下面这样通过交叉连接生成笛卡尔积，就可以得到有序对。

1
2
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;

这次的处理结果依然是有序对。接下来进一步对（苹果，橘子）和（橘子，苹果）这样只是调换了元素顺序的对进行去重。

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

在需要获取列的组合时，经常需要用到“非等值自连接”。

删除重复行

假设在这张商品表里，“橘子”这种商品存在重复。可怕的是，这张表里连主键都没有。现在就需要马上清理一下，去掉重复行。

通常，如果重复的列里不包含主键，就可以用主键来处理，但像这一样所有的列都重复的情况，则需要使用由数据库独自实现的行ID。在下面的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);

-- 非等值连接
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);

查找局部不一致的列

假设有下面这样一张住址表，主键是人名，同一家人家庭ID一样。

一般来说，同一家人应该住在同一个地方。那么该如何找出“是一家人但住址却不同的记录”呢？
实现办法有几种，不过如果用非等值自连接来实现，代码会非常简洁。

SELECT DISTINCT A1.name, A1.address
FROM Addresses A1, Addresses A2
WHERE A1.family_id = A2.family_id
      AND A1.address <> A2.address;

排序

现在，要按照价格从高到低的顺序，对下面这张表里的商品进行排序。让价格相同的商品位次也一样，而紧接着它们的商品则有两种排序方法，一种是跳过之后的位次，另一种是不跳过之后的位次。

如果使用窗口函数，可以像下面这样实现。

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;

如果修改称COUNT(DISTINCT P2.price)，那么存在相同位次的记录时，就可以不跳过之后的位次，而是连续输出（相当于DENSE_RANK函数）。由此可知，这条SQL语句可以根据不同的需求灵活地进行扩展，实现不同的排序方式。

这个子查询的代码还可以像下面这样按照自连接的写法来改写。

SELECT P1.name,
       MAX(P1.price) AS price,
       COUNT(P2.name) + 1 AS rank_1
FROM Products P1
LEFT OUTER JOIN Products P2 ON P1.price < P2.price
GROUP BY P1.name
ORDER BY rank_1;