MySQL学习之自连接

自连接的用法

什么是自连接

自连接，故名思意就是一张表与自己连接。文字语言可能很苍白，换成SQL语言会更清晰一些。
比如说有一张水果表fruit,如下图：

+----+------+------+
| Id | Name | Price|
+----+------+------|
| 1  |apple | 8.00 |
| 2  |banna | 4.00 |
| 3  |orange| 3.50 |
+----+------+------|

friut表自连接代码如下：

SELECT 
f1.*, f2.*
FROM friut f1, friut f2

代码也是很简单，需要注意的是自连接的时候，需要取别名。自连接的结果如下：

Id	Name	Price	Id(1)	  Name(1)  Price(1)
1	apple	  8	     1	    apple	  8
2	banna	  4	     1	    apple	  8
3	orange	 3.5	   1	    apple	  8
1	apple	  8	     2	    banna	  4
2	banna	  4	     2	    banna	  4
3	orange	 3.5	   2	    banna	  4
1	apple	  8	     3	    orange	 3.5
2	banna	  4	     3	    orange	 3.5
3	orange	 3.5   	3	    orange	 3.5

可以看出，查询出来的结果就是f1 x f2的结果，即笛卡尔积。在这张表的数据中，就是用f1的3条数据分别与f2的3条数据相乘，得出3 x 3即9条数据。

自连接的应用

自链接可重排列、排列、组合。
应用场景：假设一张存放了商品名称及价格的表，表里有“苹果、香蕉、橘子”三条记录。在生成用于查询销售额的报表时，需要获取这些商品的组合。

（1）可重排列

通过交叉连接生成笛卡尔积，可以得到有序对，上面提到的简单的自连接们就可以实现。

SELECT 
f1.Name fname1, f2.Name fname2
FROM friut f1, friut f2

（2）排列

可去掉由相同元素构成的对，SQL如下：

SELECT
f1.Name fname1, f2.Name fname2
FROM friut f1, friut f2
WHERE f1.Name <> f2.Name

查询结果：

fname1	fname2
banna 	apple
orange	apple
apple 	banna
orange	banna
apple 	orange
banna 	orange

（3）组合

上面的排列结果还是有序的，比如说banner -> apple 和apple -> banna是两种结果，组合就是不存在前后顺序，可以对对调换了元素顺序的对进行去重，SQL如下：

SELECT 
f1.Name fname1, f2.Name fname2
FROM friut f1, friut f2
WHERE f1.Name > f2.Name

查询结果：

fname1	fname2
banna	 apple
orange	apple
apple	banna

如果想要3个以上元素的组合，可以简单拓展一下：

SELECT 
f1.Name fname1, f2.Name fname2,f3.Name fname3
FROM friut f1, friut f2, friut f3
WHERE f1.Name > f2.Name
AND f2.Name > f3.Name

查询结果：

fname1	fname2	fname3
banna	apple	orange

自连接应用

题目：现有如下的水果表，求价格相同的水果

Id	Name	Price
1	apple	8
2	banna	4
3	orange	3.5
4	watermelon	4
5	lemon	8

SQL如下：

SELECT
f1.Name fname1, f2.Name fname2
FROM friut f1,friut f2
WHERE f1.Price = f2.Price
AND f1.Name > f2.Name

查询结果：

fname1	fname2
lemon	apple
watermelon	banna

END

以上就是今天的SQL学习，主要是关于SQL的自连接，即一张表与自身表的连接，自连接可以实现可重排列、排列、组合，在日常开发中还是有很多应用场景。有不对的地方，望指正