In MySQL, the statistical data is also shown in time and space

At work, there is a need to count 24 hours of relevant data. Some friends think that a simple group by can be solved. In fact, the real statistics, 24 hours have to have data, do not allow the absence of the phenomenon. Actually this is a bit like nonsense, processing result set resultset can also, but in order to reduce the burden of Java and enhance the readability of Java code, so try to use SQL statements, one-time data processing.

Table structure, with three fields you can see it.

SQL code

CREATE TABLE ' vardata ' (  
  ' Id ' int () NOT NULL auto_increment,  
  ' name ' varchar (255) DEFAULT null,  
  ' time ' datet IME default ' 0000-00-00 00:00:00 ',  
  ' data ' double (15,5) Default ' 0.00000 ',  
  PRIMARY KEY (' Id ', ' time '),  
  Unique KEY ' unique ' (' name ', ' time ')  

Below I want to count 12 months of monthly data, if the data exists, the data is displayed, and if the data does not exist, the error code (-601) that we have agreed to. The following is just a sample of 3 SQL statements provided. No matter if there is no data, you will definitely find 3 records.

In real development, put the time in the front of it and the time in the back where you want to assemble it dynamically with the query statement. If your friends don't understand, leave me a message ^-^

Select if (count (*) =0,-601,data) as data, ' 2011-07-12 10:40:00 ' as time from Vardata where time= ' 2011-07-12 '
u Nion
Select if (count (*) =0,-601,data) as data, ' 2012-08-12 10:40:00 ' as time from Vardata where Time= ' 2012-08-12 10:40: '
Union
Select if (count (*) =0,-601,data) as data, ' 2013-09-12 10:40:00 ' as time from Vardata where Time= ' 2013-09 -12 10:40:00 ';