实战 MySQL 多表关联查询技巧

转载，原文地址 https://mp.weixin.qq.com/s/FJHodWFi_-0eMZhrJVR9PA

示例表 A:

author_id	author_name
1	Kimmy
2	Abel
3	Bill
4	Berton

示例表 B:

book_id	author_id	start_date	end_date
9	1	2017-09-25 21:16:04	2017-09-25 21:16:06
10	3
11	2	2017-09-25 21:21:46	2017-09-25 21:21:47
12	1
13	8

示例表 C:

order_id	book_id	price	order_date
1	9	0.2	2017-09-24 21:21:46
2	9	0.6	2017-09-25 21:16:04
3	11	0.1	2017-09-25 21:21:46

在以上表中执行 AB 表关联

1 2	SELECT `authors`.*, `books`.book_id FROM `authors` LEFT JOIN `books` ON `authors`.author_id = `books`.author_id

结果

author_id	author_name	book_id
1	Kimmy	9
3	Bill	10
2	Abel	11
1	Kimmy	12
4	Berton

结果出现了 2 条 author_id 为 1 的记录，因为右表中存在了两条关联 author_id=1 的行

右边出现 N 条关联左边的记录，结果就会相应出现 N 条关联了右表出现的记录

在以上表中执行 ABC 表关联

1
2
3

SELECT `authors`.*, `books`.book_id, `orders`.order_id, `orders`.price FROM `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id

结果

author_id	author_name	book_id	order_id	order_price
1	Kimmy	9	1	0.2
1	Kimmy	9	2	0.6
2	Abel	11	3	0.1
3	Bill	10
1	Kimmy	12
4	Berton

结果出现了 3 条 author_id=1 的记录，因为 authors 第一次关联了 books 表 book_id 为 9 和 12 的 book 关联了 author_id 为 1 的作者，而 book_id 为 9 的书本则关联了两个 orders 记录，所以结果集包含 3 条 author_id 为 1 的记录

可以运用

1	count(),sum()

等函数通过

group by

来统计结果

SELECT `authors`.*, sum(`orders`.price) FROM `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
GROUP BY `books`.book_id

结果集会基于 book_id 来统计每一本书的订单总额

author_id	author_name	book_id	sum(order_price)
4	Berton
1	Kimmy	9	0.80
3	Bill	10
2	Abel	11	0.10
1	Kimmy	12

book_id 为 9 的订单总额为 0.80，并且 9 的记录从多条合并为 1 条。

多条件 join

SELECT `authors`.*, `books`.book_id, `orders`.order_id, sum(`orders`.price) FROM `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id AND `orders`.order_date >= `books`.start_date AND `orders`.order_date <= `books`.end_date
GROUP BY `books`.book_id

选取在一定时间区间范围内的 order 订单，可以看到订单 order_id 为 1 的订单不再纳入 book_id 为 9 的统计当中，因为它的时间区间不符合 join 条件

author_id	author_name	book_id	order_id	sum(`order`.price)
4	Berton
1	Kimmy	9	2	0.60
3	Bill	10
2	Abel	11	3	0.10
1	Kimmy	12

关于 where 的使用，看下面示范

SELECT `authors`.*, `books`.book_id, `orders`.order_id, sum(`orders`.price) AS prices FROM `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id AND `orders`.order_date >= `books`.start_date AND `orders`.order_date <= `books`.end_date
WHERE prices is not NULL
GROUP BY `books`.book_id

以上语句假设选取 price 不为空的记录，导致了一个错误的出现

1	[Err] 1054 - Unknown column 'prices' in 'where clause'

因为 where 不能用于选取列的 AS 别名判断，MYSQL 的处理机制是先进行选取，再进行筛选，在选取阶段就启用了 where 条件，因为这时并不存在 prices 的筛选结果后才产生的字段，所以这里会抛出错误

我们可以这样做

SELECT `authors`.*, `books`.book_id, `orders`.order_id, sum(`orders`.price) AS prices FROM `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id AND `orders`.order_date >= `books`.start_date AND `orders`.order_date <= `books`.end_date
WHERE `orders`.price is not NULL
GROUP BY `books`.book_id

选取阶段 order 表是存在 price 字段的，所以只有 price 不为空的记录才会被选取

author_id	author_name	book_id	order_id	prices
2	Abel	11	3	0.10
1	Kimmy	9	2	0.60

运用

having

对那些无法进行 WHERE 的 AS 别名的字段进行一些筛选查询

SELECT `authors`.*, `books`.book_id, sum(`orders`.price)AS prices FROM `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
GROUP BY `books`.book_id
HAVING prices > 0.1

这时只有 sum 为 0.8 的结果被选中

author_id	author_name	book_id	sum(order_price)
1	Kimmy	9	0.80

对于组合其他语法查询，也是没问题的

SELECT `authors`.*, `books`.book_id, sum(`orders`.price)AS prices FROM `authors`
LEFT JOIN `books` ON `authors`.author_id = `books`.author_id
LEFT JOIN `orders` ON `books`.book_id = `orders`.book_id
GROUP BY `books`.book_id
HAVING prices >= 0.1
ORDER BY prices asc
LIMIT 1,1