SQL进阶（二）自连接

自连接

一、简述

通常SQL 在不同的表间进行连接运算，如内连接、外连接、交叉连接等，但是也可以在相同的表进行自连接运算。

使用场景

• 可重排列
• 排列
• 组合
• 查重
• 排序

二、使用场景示例

问题描述：

分别查询出指定表中可重排列、排列、组合的结果

数据表格：product

name(商品名称)	price(价格)
苹果	50
橘子	100
香蕉	80

统计结果：可重排列

name_1	name_2
苹果	苹果
苹果	橘子
苹果	香蕉
橘子	苹果
橘子	橘子
橘子	香蕉
香蕉	苹果
香蕉	橘子
香蕉	香蕉

分析

通过交叉连接生成笛卡尔积，就可以得到以上结果。因为是可重排序，结果行数为 3²=9。

SELECT p1.name AS name_1, p2.name AS name_2
FROM product p1 CROSS JOIN product p2;

也可通过另一种常见的写法，来实现这个结果。

SELECT p1.name AS name_1, p2.name AS name_2
FROM product p1, product p2;

统计结果：排列

name_1	name_2
苹果	橘子
苹果	香蕉
橘子	苹果
橘子	香蕉
香蕉	苹果
香蕉	橘子

分析

排列不同于可重排序，排列是不包含由相同元素构成的有序对。为了去掉这种由相同元素构成的对，需要加上一个条件，然后再进行连接运算。由于排除了相同的对，因此它的行数为 P²₃ = 6。

SELECT p1.name AS name_1, p2.name AS name_2
FROM product p1 CROSS JOIN product p2
WHERE p1.name <> p2.name;

排列-百度百科

统计结果：组合

name_1	name_2
苹果	橘子
香蕉	苹果
香蕉	橘子

分析

组合其实就是无序对，通过使用字符顺序进行排列，只与字符顺序比自己靠前的商品进行配对。这种方式结合了非等值连接和自连接`，结果行数为C²₃ = 3。

SELECT p1.name AS name_1, p2.name AS name_2
FROM product p1 CROSS JOIN product p2
WHERE p1.name > p2.name;

问题描述：

查询出指定表中的重复数据

数据表格：product

rowid(行ID)	name(商品名称)	price(价格)
1	苹果	50
2	橘子	100
3	橘子	100
4	橘子	100
5	香蕉	80

统计结果

rowid	name
3	橘子
4	橘子

分析

重复的数据表示为商品名称和价格相同，但行ID不同。通过使用非等值连接，便可以查询出来。

SELECT *
FROM product p1
WHERE EXISTS (SELECT *
             FROM product p2
             WHERE p1.name = p2.name
               AND p2.price = p2.price
			   AND p1.id > p2.id);

问题描述：

将以下商品按照价格从高到底排序，针对价格相同的商品位次， `rank_1` 为 `跳过之后的位次`，`rank_2` 为 `不跳过之后的位次`。

数据表格：product

name(商品名称)	price(价格)
苹果	50
橘子	100
葡萄	50
西瓜	80
柠檬	30
香蕉	50

统计结果

name	price	rank_1	rank_2
橘子	100	1	1
西瓜	80	2	2
葡萄	50	3	3
香蕉	50	3	3
苹果	50	3	3
柠檬	30	6	4

分析

为了获取到价格从高到低名次，就需要计算出价格比自己高的记录条数，并将其作为自己的位次。

首先是价格最高的100，因为不存在比它高的价格，所以记录为0。加下来是价格第二高的80，比它高的价格只有100，所以记录为1。同样地，价格为50的时候返回2，为30的时候返回3。

集合	价格	比自己高的价格	比自己高的价格的个数
S0	100	-	0
S1	80	100	1
S2	50	100，80	2
S3	30	100，80，50，50，50	5

SELECT p1.name, p1.price,
	COUNT(p2.price) + 1 AS rank_1, -- 跳过之后的位次
	COUNT(DISTINCT p2.price) + 1 AS rank_2 -- 不跳过之后的位次
FROM product p1 LEFT OUTER JOIN product p2
  ON p1.price < p2.price
GROUP BY p1.name
ORDER BY COUNT(p2.price);

以上的 SQL 中通过使用 DISTINCT 关键字，当存在相同位次的记录时，就可以不跳过之后的位次。