本文根据《SQL进阶教程》([日]MICK 吴炎昌/译)所写笔记。
之前学习的内连接,外连接,交叉连接都是针对不同表的,本节我们学习自联结,是针对相同表的连接。
可重排列、排列、组合
我们先创建一张Products表,。在生成用于查询销售额的报表时,我们有时候会需要获得这些商品的组合:
CREATE TABLE Products
(name VARCHAR(16) PRIMARY KEY,
price INTEGER NOT NULL);
--可重排列·排列·组合
INSERT INTO Products VALUES('苹果', 50);
INSERT INTO Products VALUES('橘子', 100);
INSERT INTO Products VALUES('香蕉', 80);
mysql> select * from products;
+--------+-------+
| name | price |
+--------+-------+
| 橘子 | 100 |
| 苹果 | 50 |
| 香蕉 | 80 |
+--------+-------+
组合分两种类型。一种是有顺序的有序,另一种是无顺序的无序对。有序对用尖括号括起来,无序对用花括号括起来,这两类分别对应着“排列”和“组合”。
SQL生成有序对很简单。通过交叉连接生成笛卡尔积。
mysql> select p1.name as name_1,p2.name as name_2
-> from products p1,products p2;
+--------+--------+
| name_1 | name_2 |
+--------+--------+
| 橘子 | 橘子 |
| 苹果 | 橘子 |
| 香蕉 | 橘子 |
| 橘子 | 苹果 |
| 苹果 | 苹果 |
| 香蕉 | 苹果 |
| 橘子 | 香蕉 |
| 苹果 | 香蕉 |
| 香蕉 | 香蕉 |
+--------+--------+
-- 法二:
mysql> select p1.name as name_1,p2.name as name_2
-> from products p1 cross join products p2;
上面生成的序列是有序的,那么无序的怎么生成呢?首先,我们先去掉类似(苹果,苹果)这样的重复对:
mysql> select p1.name as name_1,p2.name as name_2
-> from products p1,products p2
-> where p1.name<>p2.name;
+--------+--------+
| name_1 | name_2 |
+--------+--------+
| 苹果 | 橘子 |
| 香蕉 | 橘子 |
| 橘子 | 苹果 |
| 香蕉 | 苹果 |
| 橘子 | 香蕉 |
| 苹果 | 香蕉 |
+--------+--------+
接下来,我们进一步对(苹果,橘子)和(橘子,苹果)这样只是调换了元素顺序的对进行去重。
mysql> select p1.name as name_1,p2.name as name_2
-> from products p1,products p2
-> where p1.name>p2.name ;
+--------+--------+
| name_1 | name_2 |
+--------+--------+
| 苹果 | 橘子 |
| 香蕉 | 橘子 |
| 香蕉 | 苹果 |
+--------+--------+
-- 想要获得三个以上元素的组合时,将上面的代码进行简单的扩展即可
mysql> select p1.name as name_1,p2.name as name_2,p3.name as name_3
-> from products p1,products p2,products p3
->