MySQL查询、统计最近30天的数据

统计数据中最近三十天各天个数

 MySQL统计最近三十天 update_time（更新时间）字段中 各天个数，并将时间转变为 2020-12-02 的日期格式

SQL代码

SELECT COUNT(*),DATE_FORMAT(update_time,'%Y-%m-%d') as time FROM `alarm` where DATE(update_time)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) GROUP BY time

执行结果

 2020-11-01不是30天内的数据 

说明

1.时间转变为 2020-12-02 的日期格式

DATE_FORMAT(update_time,'%Y-%m-%d')

查询数据中最近三十天各天个数

统计最近30天各天warmingValue字段的操作（下面代码以 求总和 举例子）

SQL代码

SELECT lefttable.date,IFNULL(righttable.m,'0') as money
FROM 
(SELECT date_sub(CURDATE(),interval @i:=@i+1 day) as date 
from ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all   select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all  select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all  select 1 union all select 1) as tmp,(select @i:= -1) t) as lefttable
LEFT JOIN 
(SELECT sum(warningValue) as m ,DATE_FORMAT(update_time,'%Y-%m-%d') as gptime 
from alarm
where DATE(update_time)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) 
GROUP BY gptime)
as righttable
ON 
lefttable.date=righttable.gptime

执行结果

说明

可以生成 最近30天的日期 然后 left join 对应的表或者字段，这样就是那天没有数据（数据为0）也能用 IFNULL()函数 设置为0 了。

1.生成最近30天的日期序列：

 

 

---

参考：https://blog.csdn.net/axela30w/article/details/76507021