Mysql collects data in the last 30 days, and no data is filled with 0. This should be a requirement that we often encounter when we do statistical analysis.

The general implementation is to group by date, but there is a problem with this, if there is no data in the database table for a day, then the statistics will not be able to calculate the results. Like the image below

2020-01-01  10
2020-01-03  20
2020-01-04  4
Copy the code

At this time, we found that there is no data on 2020-01-02. We hope that the date without data can also be returned, and the corresponding data is 0. The expectation is as follows:

2020-01-01  10
2020-01-02  0
2020-01-03  20
2020-01-04  4
Copy the code

At this point, group by alone cannot be implemented.

So what should we do?

The general situation is that we should first get a date of the virtual table, the time of the 30 days are listed, and then use the date of the virtual table to associate our business table, associated with no data value set to empty, so how to get nearly 30 days of the date, give SQL implementation

SELECT
    @s := @s + 1 AS indexs,
    DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE.INTERVAL @s DAY)),'%Y-%m-%d' ) AS dates 
FROM
    mysql.help_topic,
    ( SELECT @s := - 1) Temp # does not want to include the day,@s:=0
WHERE
    @s < 30 
ORDER BY
    dates 
Copy the code

The result of the run is as follows

Of course, can be sorted according to their own needs, time has, then directly associated with their own business table can be, give demo:

SELECT
	date_table.dates AS dateValue,
	IFNULL( temp.count, 0 ) AS count 
FROM
	(
	SELECT
		@s := @s + 1 AS indexs,
		DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE.INTERVAL @s DAY)),'%Y-%m-%d' ) AS dates 
	FROM
		mysql.help_topic,
		( SELECT @s := 0 ) temp 
	WHERE
		@s < 30 
	ORDER BY
		dates 
	) date_table
	LEFT JOIN (
	SELECT LEFT
		( create_time, 10 ) AS dateValue,
		count( * ) AS count 
	FROM
		monitor_log_record t1 
	WHERE
		t1.log_type = 1 
		AND t1.error_type = 1 
		AND t1.project_id = 1 
	GROUP BY
		LEFT ( create_time, 10 ) 
	) temp ON date_table.dates = temp.dateValue 
ORDER BY
	date_table.dates DESC
Copy the code

All right, take notes. I hope it helps. If you have any help, please remember to add your favorite collection and attention, thank you!!