MySQL如何按天统计数据,没有记录的天自动补充0

2018-02-27 10:58:23来源:oschina作者:兜兜毛毛人点击

分享

思路:


1. 通过该语句可以实现指定日期到当前日期的日期列表数据,并用0填充要统计的sum字段


SELECT @cdate := date_add(@cdate,interval - 1 day) `date` , 0 as `sum`
from (SELECT @cdate :=date_add(CURDATE(),interval + 1 day) from <任意有数据表名>) t1
where @cdate > '2017-08-03'

2.要统计的SQL语句,如:


select DATE_FORMAT(createTime,'%Y-%m-%d') as `date`,sum(money)
from payTable
group by `date`

3.合并两个结果集,并保证字段相同。完整代码如下:


select `date` ,max(`sum`) as `sum` from
(
SELECT @cdate := date_add(@cdate,interval - 1 day) `date` , 0 as `sum`
from (SELECT @cdate :=date_add(CURDATE(),interval + 1 day) from <任意有数据表名>) t1
where @cdate > '2017-08-03'
union all
select DATE_FORMAT(createTime,'%Y-%m-%d') as `date`,sum(money)
from payTable
group by `date`
) _tmpAllTable
group by `date`

以上SQL未考虑性能问题,在实际使用中慎重考虑

微信扫一扫

第七城市微信公众平台