当前位置： 移动技术网 > IT编程>数据库>Mysql > MySQL实现按天统计数据的方法

MySQL实现按天统计数据的方法

2019年03月15日  | 移动技术网IT编程  | 我要评论

一、首先生成一个日期表，执行sql如下：

create table num (i int);
insert into num (i) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
create table  if not exists calendar(datelist date);  insert into calendar(datelist) select
    adddate(
        (   
            date_format("2019-1-1", '%y-%m-%d') 
        ),
        numlist.id
    ) as `date`
from
    (
        select
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 as id
        from
            num n1
        cross join num as n10
        cross join num as n100
        cross join num as n1000
        cross join num as n10000
    ) as numlist;

二、按天统计所需数据sql如下：

select
    date(dday) ddate,
    max(registernum) as registernum, 
    max(rechargenum) as rechargenum,
    max(rechargetotal) as rechargetotal
from
    (
        select
            datelist as dday,0 as registernum,0 as rechargenum,0 as rechargetotal
        from
            calendar 
            where  1  and date_sub(curdate(), interval 365 day) <= date(datelist)&&date(datelist)<=curdate()
        union all
            select  from_unixtime(a.time,"%y-%m-%d") as dday, 0 as registernum,count(distinct(a.user_id)) as rechargenum,sum(a.money) as rechargetotal from
               top_up as a left  join referee as b on a.user_id=b.referee_id
               left join channel_user as c on b.user_id = c.uid where 1  and c.uid=1087 and a.status=2
               group by dday  
         union all
            select  from_unixtime(a.time,"%y-%m-%d") as dday, count(a.referee_id) as registernum,0 as rechargenum,0 as rechargetotal from
               referee as a 
               left join channel_user as b on a.user_id = b.uid where 1  and b.uid=1087
               group by dday
    ) a
group by ddate
order by ddate desc limit 0,10

以上统计数据可根据自身统计需求修改。

三、执行效果如下图：

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网