在做一些业务的时候,我们有时间要做订单的日报、周报、年报
第一:日报
假如我们的表中有三条数据,那我们怎么生成2021-12-10号到2021-12-16号的日期呢
其实这个sql也很好理解
select d.date,IFNULL(sum(c.amount),0) as amount,count(c.time) as count from
(
select date_add('2021-12-10 ’ ,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 ) as tmp,
(select @i:= -1) t
) as d
left join
(select * from bell_bash where count =1 and time >= '2021-12-10 ’ and time<= ‘2021-12-16 ‘) as c on d.date = DATE_FORMAT(c.time,’%Y-%m-%d’) group by d.date order by d.date d