When MySQL queries statistics by date (group by), there is no workaround for data 0

Reprinted from: http://blog.csdn.net/jie11447416/article/details/50887888

1, the data structure and data in the case are as follows

2, in the absence of resolution, is this

1 SELECT Date (downtime) as DDay, COUNT (*) as num from Re_device GROUP by DDay

Get the following result, if there is no data that day, then there will be no record

We see, the time is not continuous, no 2016-3-05 this day , this is not a problem, but, I took out the data, but also to draw a chart Ah, no, of course, we need is the following look.

Simply put, there is no data, you need to add a 0.

3, the following we say to achieve

We want to generate a calendar table, and then the original data, the joint query, said here, we know very low, but limited to my level, research this problem, half a day, this is I found a better way to achieve. If you are better, please tell me.
Execute the following SQL, the table that generates the calendar directly (calendar)

CREATE TABLE num (iint);--Create a table to store 0-9 number INSERT into num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);--Generate 0-9 number, convenient to calculate time later create TABLEifNot EXISTS calendar (datelist date); --generate a table that stores the date, DataList is the field name--here is the build and insert date data insert into calendar (datelist) SELECT adddate (--Here's the start date, you can switch to the current date Date_format ("2016-1-1", '%y-%m-%d ') , numlist.id) as ' Date ' from (SELECT n1.i+ n10.i * + n100.i * + n1000.i * 1000+ n10000.i * 10000As ID from num N1 cross join NUM as N10 Cross join Num as n100 cross join NUM As n1000 cross JOIN Num as n10000) as numlist;

Here I used 100,000 records, calculated out to 2,289 years, fully enough, to that time, problems, I can not control.

When you are finished, delete the zero of num table

4. Joint Inquiry

1 SELECT2 Date (dday) ddate,3COUNT (*)-1As num4  from5     (6 SELECT7 Datelist as DDay8  from9 CalendarTen--This is the limit for returning data for the last 30 days Onewhere Date_sub (Curdate (), INTERVAL Day) <= DATE (datelist) &&date (datelist) <=curdate () A UNION All - SELECT - Downtime the  from - Re_device - ) A -GROUP by Ddate

5. Other Solutions
Of course, there should be other solutions, but the blogger did not write, there is time to write.
I use the spring MVC, so, also can be added in the Java code complete, because the data returned is a map object, then we want to traverse this object, directly with the Calendar object, generate date as key to traverse, if there is no data, put in a 0, Then in the limit, how many days it will take. The data is complete.
However, there is also a problem, that is, the data order in the map is problematic, so, when used, it must be generated calendar object, and then construct the date as a key to traverse. or sort with collections.sort ().

I personally think, or the data directly generated a little better.

When MySQL queries statistics by date (group by), there is no workaround for data 0