[MySQL] multi-table association query tips

Example Table A:

author_id	Author_name
1	Kimmy
2	Abel
3	Bill
4	Berton

Example Table 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

Example Table 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

Perform AB Table association in the table above

Results

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

As a result, 2 records with a author_id of 1 are present because two rows of associated author_id=1 exist in the right table

On the right side of the left side of the N association record, the result will appear corresponding to the right table is associated with the record

Perform an ABC table association in the table above

SELECT ' authors '. *, ' books '. book_id, ' orders '. order_id, ' orders '. Price from ' authors ' left JOIN ' books ' on ' authors '. Auth or_id = ' books '. author_id left JOIN ' orders ' on ' books '. book_id = ' orders '. book_id

Results

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

The result was a record of 3 author_id=1, because authors first associated the books table book_id for 9 and 12, the author of author_id 1, while the BOOK_ID 9 book associated two Orders records, So the result set contains 3 records with a author_id of 1

can use

Count (), SUM ()

Such functions are passed

GROUP BY

To count the results.

SELECT ' authors '. *, sum (' orders '. Price) from ' authors ' to ' left JOIN ' books ' to ' authors '. author_id = ' books '. author_id left J OIN ' orders ' on ' books '. book_id = ' orders '. Book_idgroup by ' books '. book_id

The result rally is based on book_id to count the total order of each book.

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

The total order for the book_id 9 is 0.80, and 9 of the records are merged from multiple lines to 1

Use

Having

Some filter queries for those where keywords cannot be used with aggregate functions

SELECT ' authors '. *, ' books '. book_id, sum (' orders '. Price) as prices from ' authors ' left joins ' books ' on ' authors '. author_id = ' books '. author_id left JOIN ' orders ' to ' books '. book_id = ' orders '. Book_idgroup by ' books '. book_id have prices > 0 .1

At this point only the result of sum 0.8 is selected

author_id	Author_name	book_id	SUM (order_price)
1	Kimmy	9	0.80

It's no problem to combine other syntax queries

SELECT ' authors '. *, ' books '. book_id, sum (' orders '. Price) as prices from ' authors ' left joins ' books ' on ' authors '. author_id = ' books '. author_id left JOIN ' orders ' on ' books '. book_id = ' orders '. Book_idgroup by ' books '. BOOK_ID has prices >= 0.1 ORDER by Prices Asclimit

　　

Multi-Conditional 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 >= ' Boo KS '. start_date and ' orders '. Order_date <= ' books '. End_dategroup by ' books '. book_id

By selecting order orders within a certain time interval, you can see that orders with orders order_id of 1 are no longer included in the BOOK_ID 9 statistic because their time intervals do not match the join condition

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

[MySQL] multi-table association query tips