mysql 统计数据，按照日期分组，把没有数据的日期也展示出来

 

 

因为业务需求，要统计每天的新增用户并且要用折线图的方式展示。

如果其中有一天没有新增用户的话，这一天就是空缺的，在绘制折线图的时候是不允许的，所有要求把没有数据的日期也要在图表显示。

 

查询2019-01-10------2019-01-20日的新增用户 ，查询出来是这样的。。。。。。完全不可以绘制图表

因为绘制图表要的是这样的数据

所以写了以下sql生成绘制图表的数据。 

 

帮助有需要的人

---------------------------------------------------------------------------------------------------------------------------------------------------------

select sum(count) as count, regeistDates from (
select count(*) count, date_format(regeistDate,'%Y-%m-%d') regeistDates from t_account a
where a.regeistDate>='2018-12-21 00:00:00' and a.regeistDate<='2019-01-21 23:00:00'
GROUP BY regeistDates
UNION ALL
select @uu:=0 as count,regeistDates from (
select @num:=@num+1 as number,date_format(adddate('2018-12-21 00:00:00', INTERVAL @num DAY),'%Y-%m-%d') as regeistDates
from t_account a ,(select @num:=-1) t
where adddate('2018-12-21 00:00:00', INTERVAL @num DAY) < date_format('2019-01-21 23:00:00','%Y-%m-%d')
order by regeistDates ) rr
) sss GROUP BY sss.regeistDates ORDER BY regeistDates

---------------------------------------------------------------------------------------------------------------------------------------------------------

sql 的唯一的缺点是日期的排列是按照  t_account  这张表的行数计算的

注：

t_account    为你的数据中的一张表

 

 

select @num:=@num+1 as number,date_format(adddate('2018-12-21 00:00:00', INTERVAL @num DAY),'%Y-%m-%d') as regeistDates

from t_account a ,(select @num:=-1) t 

以上这个sql 是重点

 

引申：

补充按照每周每月统计

===================================按照周统计============================================

SELECT sum(count)as count, DATE_FORMAT(a.regeistDates,'%Y-%m-%d %u') as regeistDates FROM (
SELECT * FROM (
SELECT COUNT(t.id) as count ,date_format( t.regeistDate,'%Y-%m-%d') as regeistDates FROM t_account t
where  t.regeistDate>='2019-01-27 00:00:00' and t.regeistDate<='2019-03-27 00:00:00'
GROUP BY DATE_FORMAT(t.regeistDate,'%Y%u')
UNION ALL
select @uu:=0 as count,regeistDates from (
select @num:=@num+1 as number,date_format(adddate('2019-03-27 00:00:00', INTERVAL @num DAY),'%Y-%m-%d') as regeistDates
from t_order a ,(select @num:=-1) t
where adddate('2019-01-27 00:00:00', INTERVAL @num day) < date_format('2019-03-27 00:00:00','%Y-%m-%d')
order by regeistDates ) rr
) tt ORDER BY tt.regeistDates
) a GROUP BY DATE_FORMAT(a.regeistDates,'%Y%u')

=====================================按照月统计===========================================

SELECT sum(count)as count, a.regeistDates FROM (
SELECT * FROM (
SELECT COUNT(t.id) as count ,date_format( t.regeistDate,'%Y-%m') as regeistDates FROM t_account t
where t.regeistDate is not null and t.accountalias='39d0dc010ac646f88e3bf900f6fa6d33'
and t.regeistDate>='2019-01-27 00:00:00' and t.regeistDate<='2019-03-27 00:00:00'
GROUP BY DATE_FORMAT(t.regeistDate,'%Y-%m')
UNION ALL
select @uu:=0 as count,regeistDates from (
select @num:=@num+1 as number,date_format(adddate('2019-01-27 00:00:00', INTERVAL @num DAY),'%Y-%m') as regeistDates
from t_order a ,(select @num:=-1) t
where adddate('2019-01-27 00:00:00', INTERVAL @num day) < date_format('2019-03-27 00:00:00','%Y-%m-%d')
order by regeistDates ) rr
) tt ORDER BY tt.regeistDates
)a GROUP BY a.regeistDates

==================================获取日期所在周的周一日期 ===================================

SELECT t.regeistDate, DATE_ADD(date_format(t.regeistDate,'%Y-%m-%d'),INTERVAL -WEEKDAY(date_format(t.regeistDate,'%Y-%m-%d')) DAY) as regeistDates  from  t_account t 

==========================================================================================

以上只是个人见解

 

如果您有好的方法可以在此文章下进行评论

 

-------------------------------------------------------

感谢帮忙的老王 。。。。。。哈哈哈