006-VLOOKUP与JOIN

VLOOKUP

假设你有两个Excel表格，其中一个为用户信息表：

name，gender，age
小A，男，20
小B，男，21
小C，女，22
小D，女，23

另外一个为用户购买明细表：

name，order_no，order_amount
小A，1，1000
小A，2，500
小D，3，800
小D，4，900
小C，5，700
小C，6，1100
小C，7，1200
小B，8，1400

具体Excel如下：

两张表格

现在，有个需求是，求出不同性别的客单价。
对于excel来说呢，首先第一步给每个订单算出性别，这里就需要用到vlookup了：

vlookup

公式为VLOOKUP(E3,A:C,2,FALSE)，表示需要查找的值为E3（小A）、查找范围为A:C（A列到 C列）、输出第二列的值，不使用模糊搜索。然后依次求值，我们就得到了带有性别的购买明细表格。
怎么求分性别的客单价呢？因为这个涉及到了聚合（按性别聚合），所以我们想到使用透视表：

透视表求分性别客单价

JOIN

如果刚刚的两个Excel表格是MySQL表呢？我们要怎么样才能把两个表的数据放在一起呢？SQL提供了一个方案是JOIN。JOIN的用法是：

SELECT * 
FROM <表名> 
JOIN <表名> ON <表达式>
JOIN <表名> ON <表达式>
...

FROM 后面可以接多个JOIN，每个JOIN后面紧跟一个表名，然后是ON，后面紧跟一个表达式。
JOIN的逻辑是把FROM里面的表和JOIN的表，拼接成一个新表，然后符合ON表达式的行留下，其他的丢弃。那么怎样拼接的呢？

拼接方案

拼接方案为：

A表的每一行和B表的每一行组合出所有可能，所有可能性汇成一张表。

假设FROM表有N行，X个字段，JOIN表有M行Y个字段，那么拼接后的表每一行都有X+Y个字段，然后总共有N*M行。举例：

A表
A_col1,A_col2
A11,A12
A21,A22

B表
B_col1,B_col2
B11,B12
B21,B22
B31,B33

拼接后的表为：

A11,A12,B11,B12
A11,A12,B21,B22
A11,A12,B31,B33
A21,A22,B11,B12
A21,A22,B21,B22
A21,A22,B31,B33

枚举所有的组合可能性，每一行都有2+2个字段，然后刚好2*3行。

筛选

拼接之后要筛选，是利用ON后面的表达式来筛选的。比如我们要得到带有性别的购买明细表，那么SQL如下：

SELECT * FROM 购买明细 JOIN 用户信息 ON 购买明细.name = 用户信息.name

拼接表完毕之后是：

小A，1，1000，小A，男，20
小A，1，1000，小B，男，21
小A，1，1000，小C，女，22
小A，1，1000，小D，女，23
小A，2，500，小A，男，20
小A，2，500，小B，男，21
小A，2，500，小C，女，22
小A，2，500，小D，女，23
....
(仅显示部分)

其中满足购买明细.name = 用户信息.name仅有这些列：

小A，1，1000，小A，男，20
小A，2，500，小A，男，20
...
(仅显示部分)

这就是我们这条SQL跑出来的结果。这样看上去比较杂乱，因为名字显示了两次，还有我们不想要的年龄信息，所以优化一下SQL：

SELECT 
购买明细.name, 
order_no, 
order_amount, 
gender
FROM 购买明细 
JOIN 用户信息 ON 购买明细.name = 用户信息.name
(注意name字段需要指定表名，因为两张表都有name字段，如果不指定系统会不知道用哪张表的name字段)

这就得到了我们想要的结果：

小A，1，1000，男
小A，2，500，男
...
(仅显示部分)

当然，我们的目标是求出分性别的客单价，那么再使用熟悉的GROUP BY即可：

SELECT gender, avg(order_amount)
FROM 购买明细 
JOIN 用户信息 ON 购买明细.name = 用户信息.name
GROUP BY gender

总结

FROM只能从一个表里面拿数据，会有诸多限制。所以可以把JOIN看做是对FROM的补充，通过JOIN，FROM可以从多个表拿数据。整个SQL求值顺序为：

JOIN拼接 => ON筛选 => WHERE过滤 => GROUP BY聚合 
=> ORDER BY排序 => LIMIT限制输出 => SELECT表达式求值

最后编辑于：2019.07.23 23:22:04