How to realize multi-table correlation statistics in mysql

This article shows you how to achieve multi-table correlation statistics in mysql, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

Requirements:

Statistics of reward amount for each book, recharge data statistics at different times, consumption statistics,

Design four tables, book table, orders table reward_log reward table consume_log consumption table, associate with book table through book_id,

Question:

When there are more than two tables associated, the data is duplicated in statistics. We have to use subquery to find out. Subquery can only search one field. Here, CONCAT_WS function is used to concatenate multiple fields.

Achieve:

The query code is as follows

SELECTb.id,b.book_name,sum( IF ( o.create_time > 0 && o.create_time

< 9999999999, o.price, 0 ) ) today_pay_money,sum( IF ( o.create_time >

0 && o.create_time

< 9999999999, 1, 0 ) ) today_pay_num,sum( IF ( o.create_time >

999 && o.create_time

< 9999, o.price, 0 ) ) yesterday_pay_money,sum( IF ( o.create_time >

999 && o.create_time

< 9999, 1, 0 ) ) yesterday_pay_num,sum(o.price) total_pay_money,sum( IF ( o.create_time >

9999 && o.create_time

< 99999, 1, 0 ) ) total_pay_num,( SELECT SUM( total_score ) FROM book_reward_log WHERE book_id = b.id ) total_score,( SELECT CONCAT_WS( ',', SUM( IF ( create_time >

0 && create_time

< 998, score, 0 ) ), SUM( IF ( create_time >

9999 && create_time

< 99998, score, 0 ) ), SUM( IF ( create_time >

99999 && create_time

< 999998, score, 0 ) ) ) FROM book_consume_log WHERE book_id = b.id ) score FROM book_book b LEFT JOIN book_orders o ON b.id = o.bidGROUP BY b.id 查询结果

score is three consumption numbers separated by commas

performance analysis

The above content is how to achieve multi-table correlation statistics in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.