This article uses an example to describe how to implement multi-table association statistics in MySQL. Share with you for your reference, the details are as follows: need: Statistics on the reward amount for each book, recharge data statistics at different times, consumption statistics, Design four tables, book table, orders table, reward_log table, consume_log table, and associate them with book table through book_id. question: When more than two tables are associated, data duplication occurs during statistics. You have to use a subquery to find it out. A subquery can only query one field. Here, the CONCAT_WS function is used to concatenate multiple fields. accomplish: The query code is as follows SELECT b.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.bid GROUP BY b.id Query results score is three consumption numbers, separated by commas Performance Analysis Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary" I hope this article will be helpful to everyone's MySQL database design. You may also be interested in:
|
<<: Let’s talk in detail about how JavaScript affects DOM tree construction
>>: Diagram of the process of implementing direction proxy through nginx
This article shares the specific code of JavaScri...
Table of contents Linux MySQL 5.5 upgraded to MyS...
Regarding display: flex layout, some people have ...
I will be learning MySQL next semester. I didn...
Use the vscode editor to create a vue template, s...
Recently, new projects have used springcloud and ...
Preface I wrote an article about rem adaptation b...
1. Python installation 1. Create a folder. mkdir ...
After the image is built successfully, it can be ...
In Linux, we usually use the mv command to rename...
Table of contents Preface Case: Imitation of JD.c...
1. Introduction resolv.conf is the configuration ...
1. The Linux server configures /etc/hosts.deny to...
The 404 problem occurs in the Tomcat test. The pr...
In daily work, we sometimes run slow queries to r...