示例表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表关联

SELECT`authors`.*, `books`.book_idFROM`authors`
LEFTJOIN`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表关联

SELECT`authors`.*, `books`.book_id, `orders`.order_id, `orders`.priceFROM`authors`
LEFTJOIN`books`ON`authors`.author_id = `books`.author_id
LEFTJOIN`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的记录

可以运用

count(),sum()

等函数通过

groupby

来统计结果

SELECT`authors`.*,sum(`orders`.price)FROM`authors`
LEFTJOIN`books`ON`authors`.author_id = `books`.author_id
LEFTJOIN`orders`ON`books`.book_id = `orders`.book_id
GROUPBY`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`
LEFTJOIN`books`ON`authors`.author_id = `books`.author_id
LEFTJOIN`orders`ON`books`.book_id = `orders`.book_idAND`orders`.order_date >= `books`.start_dateAND`orders`.order_date <= `books`.end_date
GROUPBY`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)ASpricesFROM`authors`
LEFTJOIN`books`ON`authors`.author_id = `books`.author_id
LEFTJOIN`orders`ON`books`.book_id = `orders`.book_idAND`orders`.order_date >= `books`.start_dateAND`orders`.order_date <= `books`.end_date
WHEREpricesisnotNULL
GROUPBY`books`.book_id

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

[Err] 1054 - Unknowncolumn'prices'in'where clause'


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

SELECT`authors`.*, `books`.book_id, `orders`.order_id,sum(`orders`.price)ASpricesFROM`authors`
LEFTJOIN`books`ON`authors`.author_id = `books`.author_id
LEFTJOIN`orders`ON`books`.book_id = `orders`.book_idAND`orders`.order_date >= `books`.start_dateAND`orders`.order_date <= `books`.end_date
WHERE`orders`.priceisnotNULL
GROUPBY`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)ASpricesFROM`authors`
LEFTJOIN`books`ON`authors`.author_id = `books`.author_id
LEFTJOIN`orders`ON`books`.book_id = `orders`.book_id
GROUPBY`books`.book_id
HAVINGprices > 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)ASpricesFROM`authors`
LEFTJOIN`books`ON`authors`.author_id = `books`.author_id
LEFTJOIN`orders`ON`books`.book_id = `orders`.book_id
GROUPBY`books`.book_id
HAVINGprices >= 0.1
ORDERBYpricesasc
LIMIT 1,1