How to use one sql in mysql to encapsulate multiple sql that cannot be associated into a result set

Problem handling process

Briefly explain the business scenario. To facilitate understanding, the business requirements have been simplified.

There is now a distribution activity. Everyone can become a distributor to share the activity. Once someone purchases through the shared activity link, the distributor will have profit information. Of course, the distribution activity products can also be purchased without sharing the link. It is a direct purchase, but there will be no distribution income. In terms of table structure, all orders are stored in the order table order. For those with distribution relationships, the distribution binding information (distributor and distributed person) will be recorded in the record table. , those purchased directly without distribution will not add records to the record table. Now we are required to count the total number of orders and distribution records on that day, assuming that the day is 2022.11.08.

If it is a separate statistical calculation, it is very simple, just count the total number directly:

Count the total number of orders on the day:

SELECT COUNT(1) total_couut FROM order WHERE DATE_FORMAT(order_create_time,'%Y-%m-%d') = '2022-11-08'

Count the total number of distribution records on the day:

SELECT COUNT(1) record _count FROM record WHEREDATE_FORMAT(create_time,'%Y-%m-%d') = '2022-11-08'

But how to encapsulate two different statistical information into one result set? Here is a solution, use union all to perform parallel query, and then perform summation query. The specific implementation method is as follows.

1. Use union all for parallel query

To ensure that the queried parameter information is consistent, when querying the total number of orders, the total number of distribution records is supplemented, and when querying the total number of distribution records, the total number of orders is supplemented. Specific implementation The query results are as follows:

SELECT COUNT(1)  total_couut，0 record_count FROM order WHERE DATE_FORMAT(order_create_time,'%Y-%m-%d') = '2022-11-08'
union all 
SELECT 0 total_count，COUNT(1) record _count FROM record WHEREDATE_FORMAT(create_time,'%Y-%m-%d') = '2022-11-08'

The query results are as follows:

2.Sum processing

Now the total order number and total distribution record have been queried Numbers, what needs to be processed next is how to encapsulate them into a result set. The processing method is also very simple, that is, direct summing, because the corresponding field values ​​​​are all 0. The specific implementation is as follows:

select sum(t.total_count) total_count, sum(t.record_count) record_count from
(SELECT COUNT(1)  total_couut，0 record_count FROM order WHERE DATE_FORMAT(order_create_time,'%Y-%m-%d') = '2022-11-08'
union all 
SELECT 0 total_count，COUNT(1) record _count FROM record WHEREDATE_FORMAT(create_time,'%Y-%m-%d') = '2022-11-08') t

The query results are as follows:

The above is the detailed content of How to use one sql in mysql to encapsulate multiple sql that cannot be associated into a result set. For more information, please follow other related articles on the PHP Chinese website!