Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to fill in 0 according to the daily statistical report that day, which is very detailed and has certain reference value. Friends who are interested must finish reading it!

1. Recurrence of the problem:

Count the total number of days per day. If there are any days in which there is no data, then the group by returns will ignore those days. How to fill in 0? As shown in the figure below, of the statistical data of 10-3 days and 10-107 days, only data is available on the 8th and 10th. Returned in this way, there are only 2 data, which does not meet the needs of report statistics. Expected no value to fill in 0

two。 Think differently:

We use a set of consecutive days as the left table, and then the data to be queried by left join finally group by.: consecutive days table T1 left join business data T2 group by t1.day, as shown below:

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) 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 days, INTERVAL + 1 DAY) FROM order) t0 LIMIT 7

The implementation results are as follows:

SQL Analysis:

1. @ cdate: = defines a variable named cdate and assigns a value (select must be followed by: =)

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

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

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

5.LIMIT 7 limits the number of entries, and it's done, and we get a record of the first seven days of the specified date.

2.2 left association and then grouping

Left join group by t1.day

That is, the business data is associated according to the left table, and grouped according to the date of the left table, that is, it is divided into the specified 7-day data. If there is a record, the number of entries is counted. If there is no record, it is 0.

Final implementation result:

The above is all the contents of the article "how to fill in no data on the day according to the daily statistical report of mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.