SQL进阶教程 1-2-1 自连接的用法之排列组合

可重排列、排列、组合

---

此系列为Mick的SQL进阶教程的笔记，有错误的地方欢迎批评指正

这一部分，我们所用的数据库（表名：Products）如下：

name   price

香蕉     80

橘子     100

苹果      50

1.可重排列（可重的有序对即把所有排列的可能结果都显示）

代码：

SELECT P1.name AS name_1, P2.name AS name_2

FROM Products AS P1 CROSS JOIN Products AS P2

结果：

name_1 name_2

"苹果"    "苹果"

"苹果"    "橘子"

"苹果"    "香蕉"

"橘子"    "苹果"

"橘子"    "橘子"

"橘子"   "香蕉"

"香蕉"   "苹果"

"香蕉"   "橘子"

"香蕉"   "香蕉"

2.不可重排列（去重的有序对）

加一条筛选条件，把name_1=name_2的去除，只留下name_1不等于name_2的部分，即把1结果中斜体的部分去掉

代码：

SELECT P1.name AS name_1, P2.name AS name_2

FROM Products AS P1 CROSS JOIN Products AS P2

WHERE P1.name<>P2.name;

结果：

name_1 name_2

"苹果"    "橘子"

"苹果"    "香蕉"

"橘子"    "苹果"

"橘子"    "香蕉"

"香蕉"    "苹果"

"香蕉"    "橘子"

3.组合（无序对）

更改筛选条件，使得"苹果"    "橘子"/ "橘子"    "苹果"这样的排列只留下一个

代码：

SELECT P1.name AS name_1, P2.name AS name_2

FROM Products AS P1 CROSS JOIN Products AS P2

WHERE P1.name>P2.name; --这边>或者<都可以，这边我不太明白>和<的字符串是怎么计算的

>的结果：

name_1 name_2

"苹果"    "橘子"

"香蕉"   "苹果"

"香蕉"   "橘子"

<的结果：

name_1 name_2

"苹果"    "香蕉"

"橘子"    "苹果"

"橘子"    "香蕉"

练习题

---

1.请使用表Products，求出两列可重组合，结果应如下：

2.请使用表Products，求出如下结果：

练习题答案

---

1.

SELECT P1.name AS name_1, P2.name AS name_2

FROM Products AS P1 CROSS JOIN Products AS P2

WHERE P1.name>=P2.name

ORDER BY P1.name DESC;

2.

SELECT P1.name AS name_1, P2.name AS name_2,P3.name AS name_3

FROM Products AS P1 CROSS JOIN Products AS P2 CROSS JOIN Products AS P3

WHERE P1.name>P2.name AND P2.name>P3.name;