当前位置: 萬仟网 > IT编程>数据库>Mysql > MySQL实现按天统计数据的方法

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

12  人参与 | 时间:2019-03-15 | 我要评论

一、首先生成一个日期表,执行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

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

三、执行效果如下图:

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com