1-2 自连接的用法

前言

面向对象语言以对象的方式来描述世界，而面向集合语言 SQL 以集合的方式来描述世界。

可重排列、排列、组合

排列: 一种有序对. 有序对用尖括号括起来，如 <1, 2>. 在有序对里，如果元素顺序相反，那就是不同的对, 因此 <1, 2> ≠ <2, 1>
组合: 一种无序对. 无序对用花括号括起来，如 {1, 2}. 无序对与顺序无关，因此 {1, 2} ＝ {2, 1}

可重排列

生成有序对

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

结果

排列

排除掉由相同元素构成的对

-- 用于获取排列的SQL 语句
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;

查找局部不一致的列

找出像前田夫妇这样的是同一家人但住址却不同的记录

-- 用于查找是同一家人但住址却不同的记录的SQL 语句
SELECT DISTINCT A1.name, A1.address
FROM Addresses A1, Addresses A2
WHERE A1.family_id = A2.family_id
AND A1.address <> A2.address ;

结果

排名

mysq8.0已经支持窗口函数

-- 排序：使用窗口函数
SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rank_1,
DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2,
ROW_number() OVER (ORDER BY price DESC) AS rank_2
FROM Products;

结果

关于这三个窗口函数参考: https://www.cnblogs.com/scwbky/p/9558203.html

本节小结

自连接是不亚于 CASE 表达式的重要技术，请一定熟练掌握。下面是本节要点

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