Mysql does not enter 0 implementation code on the current day according to the daily statistical report. mysql Statistical Report

Mysql does not enter 0 implementation code on the current day according to the daily statistical report. mysql Statistical Report
1. recurrence of the problem:

Count the total number of days per day. If there is no data for a few days, the group by return will ignore those days. How to fill in 0? For example, 10-3 ~ Only data on the 8 and 10 days is returned. Therefore, only two data records are returned, which does not meet the report statistics requirements. Expected Value: 0

2. Change your mind:

We use a group of consecutive days as the left table and then left join the data to be queried. Last group by: consecutive days table t1 left join business data t2 group by t1.day, for example:

SELECT t1.`day`, COUNT(t2.user_id) payment_numFROM (SELECT @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY FROM (SELECT  @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) FROM  order) t0 LIMIT 7) t1 LEFT JOIN (SELECT  DATE(a.create_time) DAY,  a.user_id FROM  pay_payment a  JOIN dealer_store b  ON a.order_no = b.order_no WHERE DATE(create_time) <= '20171219'  AND DATE(create_time) > DATE_SUB('20171219', INTERVAL 7 DAY)  ) t2 ON t2.day = t1.dayGROUP BY t1.`day`;

2.1 consecutive days table

SELECT @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY FROM (SELECT  @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) FROM  order) t0 LIMIT 7

The execution result is as follows:

SQL analysis:

1.@ Cdate: = is to define a variable named cdate and assign values (select must be followed by: =)

2.@cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) Add one day according to the input date '20140901'

3.SELECT @cdate := DATE_ADD('20171219', INTERVAL + 1 DAY) FROM `order`  Find a table with more than 10 records. The execution result is as follows:

4.@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY Set the number of days of the defined cdate variable-1 (auto-Subtract)

5. LIMIT 7: LIMIT the number of items. As a result, we get the record from the past seven days of the specified date.

2.2 left join and then group

left join group by t1.day

That is, associate business data from the left table and group the Data Based on the date in the left table, which is divided into the specified 7-day data. If there is a record, the number of data records is counted. If there is no record, it is 0.

Final execution result:

Summary

The above is a small series of implementation code for mysql to provide zero data on the day of daily statistics report. I hope it will help you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!