select avg(amt) avgamt ,1 as beforeDay from (select * from tb where DATE_ADD(CURDATE(),INTERVAL -1 DAY)=createdate) t union all select avg(amt) avgamt ,2 as beforeDay from (select * from tb where DATE_ADD(CURDATE(),INTERVAL -2 DAY)=createdate) t union all select avg(amt) avgamt ,3 as beforeDay from (select * from tb where DATE_ADD(CURDATE(),INTERVAL -3 DAY)=createdate) t union all select avg(amt) avgamt ,4 as beforeDay from (select * from tb where DATE_ADD(CURDATE(),INTERVAL -4 DAY)=createdate) t union all select avg(amt) avgamt ,5 as beforeDay from (select * from tb where DATE_ADD(CURDATE(),INTERVAL -5 DAY)=createdate) t union all select avg(amt) avgamt ,6 as beforeDay from (select * from tb where DATE_ADD(CURDATE(),INTERVAL -6 DAY)=createdate) t union all select avg(amt) avgamt ,7 as beforeDay from (select * from tb where DATE_ADD(CURDATE(),INTERVAL -7 DAY)=createdate) t order by beforeday asc
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -7 DAY)>=createdate) t
--大致意思是这样
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
我是查过去7天每天的平均值不是7天前的 比如今天是27号 是查26号AMT平均值 25号AMT平均值... 到20号AMT平均值
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -1 DAY)=createdate) t
union all
select avg(amt) avgamt ,2 as beforeDay from
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -2 DAY)=createdate) t
union all
select avg(amt) avgamt ,3 as beforeDay from
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -3 DAY)=createdate) t
union all
select avg(amt) avgamt ,4 as beforeDay from
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -4 DAY)=createdate) t
union all
select avg(amt) avgamt ,5 as beforeDay from
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -5 DAY)=createdate) t
union all
select avg(amt) avgamt ,6 as beforeDay from
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -6 DAY)=createdate) t
union all
select avg(amt) avgamt ,7 as beforeDay from
(select * from tb where DATE_ADD(CURDATE(),INTERVAL -7 DAY)=createdate) t
order by beforeday asc
这个好像查不出来啊
我们要做个报表算出 前7天每天当天创建数据的平均值 创建的时候有 createDate 也就是有7个平均值