mysql多表连接技巧_[MySQL]多表关联查询技巧

示例表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 &＃96;authors&＃96;.*, &＃96;books&＃96;.book_id FROM &＃96;authors&＃96;

LEFT JOIN &＃96;books&＃96; ON &＃96;authors&＃96;.author_id &＃61; &＃96;books&＃96;.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的记录&＃xff0c;因为右表中存在了两条关联author_id&＃61;1的行

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

在以上表中执行ABC表关联

SELECT &＃96;authors&＃96;.*, &＃96;books&＃96;.book_id, &＃96;orders&＃96;.order_id, &＃96;orders&＃96;.price FROM &＃96;authors&＃96;

LEFT JOIN &＃96;books&＃96; ON &＃96;authors&＃96;.author_id &＃61; &＃96;books&＃96;.author_id

LEFT JOIN &＃96;orders&＃96; ON &＃96;books&＃96;.book_id &＃61; &＃96;orders&＃96;.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&＃61;1的记录&＃xff0c;因为authors第一次关联了books表book_id为9和12的book关联了author_id为1的作者&＃xff0c;而book_id为9的书本则关联了两个orders记录&＃xff0c;所以结果集包含3条author_id为1的记录

可以运用

count(),sum()

等函数通过

group by

来统计结果

SELECT &＃96;authors&＃96;.*, sum(&＃96;orders&＃96;.price) FROM &＃96;authors&＃96;

LEFT JOIN &＃96;books&＃96; ON &＃96;authors&＃96;.author_id &＃61; &＃96;books&＃96;.author_id

LEFT JOIN &＃96;orders&＃96; ON &＃96;books&＃96;.book_id &＃61; &＃96;orders&＃96;.book_id

GROUP BY &＃96;books&＃96;.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&＃xff0c;并且9的记录从多条合并为1条

运用

having

对那些WHERE 关键字无法与合计函数一起使用进行一些筛选查询

SELECT &＃96;authors&＃96;.*, &＃96;books&＃96;.book_id, sum(&＃96;orders&＃96;.price)AS prices FROM &＃96;authors&＃96;

LEFT JOIN &＃96;books&＃96; ON &＃96;authors&＃96;.author_id &＃61; &＃96;books&＃96;.author_id

LEFT JOIN &＃96;orders&＃96; ON &＃96;books&＃96;.book_id &＃61; &＃96;orders&＃96;.book_id

GROUP BY &＃96;books&＃96;.book_id

HAVING prices > 0.1

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

author_id

author_name

book_id

sum(order_price)

1

Kimmy

9

0.80

对于组合其他语法查询&＃xff0c;也是没问题的

SELECT &＃96;authors&＃96;.*, &＃96;books&＃96;.book_id, sum(&＃96;orders&＃96;.price)AS prices FROM &＃96;authors&＃96;

LEFT JOIN &＃96;books&＃96; ON &＃96;authors&＃96;.author_id &＃61; &＃96;books&＃96;.author_id

LEFT JOIN &＃96;orders&＃96; ON &＃96;books&＃96;.book_id &＃61; &＃96;orders&＃96;.book_id

GROUP BY &＃96;books&＃96;.book_id

HAVING prices >&＃61; 0.1

ORDER BY prices asc

LIMIT 1,1

多条件join

SELECT &＃96;authors&＃96;.*, &＃96;books&＃96;.book_id, &＃96;orders&＃96;.order_id, sum(&＃96;orders&＃96;.price) FROM &＃96;authors&＃96;

LEFT JOIN &＃96;books&＃96; ON &＃96;authors&＃96;.author_id &＃61; &＃96;books&＃96;.author_id

LEFT JOIN &＃96;orders&＃96; ON &＃96;books&＃96;.book_id &＃61; &＃96;orders&＃96;.book_id AND &＃96;orders&＃96;.order_date >&＃61; &＃96;books&＃96;.start_date AND &＃96;orders&＃96;.order_date <&＃61; &＃96;books&＃96;.end_date

GROUP BY &＃96;books&＃96;.book_id

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

author_id

author_name

book_id

order_id

sum(&＃96;order&＃96;.price)

4

Berton

1

Kimmy

9

2

0.60

3

Bill

10

2

Abel

11

3

0.10

1

Kimmy

12