可重排列、排列、组合
此系列为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;
网友评论