MySQL implements multi-table association statistics (subquery statistics) example

MySQL implements multi-table association statistics (subquery statistics) example

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:
  • Example of how to retrieve the latest data using MySQL multi-table association one-to-many query
  • A brief discussion on the implementation of multi-table unrelated query in MySQL
  • MySQL detailed explanation of multi-table association query

<<:  Let’s talk in detail about how JavaScript affects DOM tree construction

>>:  Diagram of the process of implementing direction proxy through nginx

Recommend

JavaScript canvas to achieve scratch lottery example

This article shares the specific code of JavaScri...

Steps and pitfalls of upgrading linux mysql5.5 to mysql5.7

Table of contents Linux MySQL 5.5 upgraded to MyS...

Display flex arrangement in CSS (layout tool)

Regarding display: flex layout, some people have ...

MySQL 8.0.19 installation and configuration tutorial under Windows 10

I will be learning MySQL next semester. I didn...

Implementation of vscode custom vue template

Use the vscode editor to create a vue template, s...

About 3 common packages of rem adaptation

Preface I wrote an article about rem adaptation b...

Implementation of pushing Docker images to Docker Hub

After the image is built successfully, it can be ...

How to Rename a Group of Files at Once on Linux

In Linux, we usually use the mv command to rename...

A brief analysis of Linux resolv.conf

1. Introduction resolv.conf is the configuration ...

Linux server SSH cracking prevention method (recommended)

1. The Linux server configures /etc/hosts.deny to...

Detailed explanation of the solution to Tomcat's 404 error

The 404 problem occurs in the Tomcat test. The pr...

MySQL Index Optimization Explained

In daily work, we sometimes run slow queries to r...