sql进阶教程02-自连接

无论表还是视图，本质上都是集合。集合是 SQL 能处理的唯一的数据结构。

1.1 自连接使用

自连接和非等值连接结合起来非常好用。

1
2
3

-- 用于获取排列的 SQL 语句
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2 WHERE P1.name <> P2.name;

无论是 P1 还是 P2，实际上数据都来自同一张物理表 Product。但是，在 SQL 里，只要被赋予了不同的名称，即便是相同的表也应该当作不同的表(集合)来对待。也就是说，P1 和 P2 可以看成是碰巧存储了相同数据的两个集合。

1.2 查找局部不一致的列

SELECT DISTINCT P1.name,P1.price FROM Products P1, Products P2 WHERE P1.name <> P2.name AND P1.price = P2.price;

/*
草莓	100
葡萄	50
香蕉	50
橘子	100
苹果	50
*/

1.3 排序

排序从 1 开始。如果已出现相同位次，则跳过之后的位次。

SELECT name, price, (SELECT COUNT(P2.price) FROM Products P2 WHERE P2.price > P1.price) +1 AS rank
FROM Products P1 order by rank

/*
草莓	100	1
橘子	100	1
西瓜	80	3
葡萄	50	4
苹果	50	4
香蕉	50	4
柠檬	30	7
*/

这道例题很好地体现了面向集合的思维方式。子查询所做的，是计算出价格比自己高的记录的条数并将其作为自己的位次。

1.4 集合包含关系

/*
柠檬	30
葡萄	50
西瓜	80
橘子	100
*/




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

/*
橘子	100	1
西瓜	80	2
葡萄	50	3
柠檬	30	4
 */

1.5 总结

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

自连接经常和非等值连接结合起来使用。
自连接和GROUP BY结合使用可以生成递归集合。
将自连接看作不同表之间的连接更容易理解。
应把表看作行的集合，用面向集合的方法来思考。
自连接的性能开销更大，应尽量给用于连接的列建立索引。