MySQL 统计过去12个月的数据(包括本月)

* from device_data t where DATE_FORMAT(t. time , ‘%Y-%m‘ )> DATE_FORMAT(date_sub(curdate(), interval 12 month ), ‘%Y-%m‘ );

2.2 统计某台设备在过去12个月中每个月body_infared大于0的数据之和。

   select  DATE_FORMAT(t. time , ‘%Y-%m‘ )  month , count (t.id)  minute   from  device_data t
 where  t.body_infrared> 0   and  t.device_id= 13  
 AND   DATE_FORMAT( time , ‘%Y-%m‘ )>
DATE_FORMAT(date_sub(curdate(),  interval   12   month ), ‘%Y-%m‘ )
  group   by  device_id, month   

获取结果：

这时候我们发现，原始数据中只有三个月的数据，还有其它9个月按要求应该是显示为0的。

2.3 用一个笨点的方法获取过去12个月所有的月份。
创建一个查找过去12个月的视图，当然这里你也可以使用存储过程遍历地把12个月插入到一个临时表中，代码会更优雅一点。

   CREATE  
    ALGORITHM = UNDEFINED 
    DEFINER =  `root` @ `%`  
     SQL  SECURITY DEFINER
 VIEW   `past_12_month_view`   AS 
     SELECT  DATE_FORMAT(CURDATE(),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   1   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   2   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   3   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   4   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   5   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   6   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   7   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   8   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   9   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   10   MONTH ),  ‘%Y-%m‘ )  AS   `month`  
     UNION   SELECT  DATE_FORMAT((CURDATE() -  INTERVAL   11   MONTH ),  ‘%Y-%m‘ )  AS   `month`   

查看12个月的显示情况：

   select  t. month   from  past_12_month_view t  group   by  t. month ;  

2.4 把2.3的12个月的显示数据与 2.2查询结果关联查询

   select  v. month ,ifnull(b. minute , 0 )   minute   from  past_12_month_view v 
 left   join 
( select  DATE_FORMAT(t. time , ‘%Y-%m‘ )  month , count (t.id)  minute  
 from  device_data t  where  
DATE_FORMAT(t. time , ‘%Y-%m‘ )>
DATE_FORMAT(date_sub(curdate(),  interval   12   month ), ‘%Y-%m‘ )
 and  t.device_id=( select  d.id  from  device d  where  d.serial= ‘01150100004‘ )
 group   by  t.device_id, month )b
 on  v. month  = b. month   group   by  v. month   

最后得到我们想要的结果：

$(function () { $(‘pre.prettyprint code‘).each(function () { var lines = $(this).text().split(‘\n‘).length; var $numbering = $(‘ ‘).addClass(‘pre-numbering‘).hide(); $(this).addClass(‘has-numbering‘).parent().append($numbering); for (i = 1; i ‘).text(i)); }; $numbering.fadeIn(1700); }); });

MySQL 统计过去12个月的数据(包括本月)

标签：mysql   过去12个月   统计每个月   data   数据   

查看更多关于MySQL 统计过去12个月的数据(包括本月)的详细内容...