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