使用mysql，统计本周，本月，本年的全部数据，没有的显示0

使用mysql，统计本周，本月，本年的全部数据，没有的显示0

 

一、原始数据

查询的原始数据表

select Create_time,userid from sys_user;

查询的结果

2023年，8月25日：有3条数据，8月18日：有1条数据，7月有1条数据

 

二、统计数据

1、统计本周

统计本周的sql

-- 本周
select a.date,IFNULL(b.sum, 0) sum 
from  ( 
    select @cdate := date_add(@cdate,interval - 1 day) as date
    from (SELECT @cdate :=date_add(last_day(curdate()),interval + 1 day) from sys_user) t1 
    where @cdate > (date_add(curdate(),interval -day(curdate())+1 day)) 
) a 
left join 
( 
    select count(*) sum ,DATE_FORMAT(Create_time, '%Y-%m-%d' ) AS date from sys_user 
    WHERE DATE_FORMAT( Create_time, '%Y%m') = DATE_FORMAT( CURDATE(), '%Y%m' ) 
    group by DATE_FORMAT(Create_time, '%Y%m%d')  
) b
on a.date =b.date 

统计本周的结果

 

由于上述示例，存在出入，请参考下面的示例

select a.date,IFNULL(b.sum, 0) sum
from (
        SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) date 
            UNION ALL
        SELECT DATE_ADD(CURDATE(), INTERVAL 1 - WEEKDAY(CURDATE()) DAY) date
            UNION ALL
        SELECT DATE_ADD(CURDATE(), INTERVAL 2 - WEEKDAY(CURDATE()) DAY) date
            UNION ALL
        SELECT DATE_ADD(CURDATE(), INTERVAL 3 - WEEKDAY(CURDATE()) DAY) date
            UNION ALL
        SELECT DATE_ADD(CURDATE(), INTERVAL 4 - WEEKDAY(CURDATE()) DAY) date
            UNION ALL
        SELECT DATE_ADD(CURDATE(), INTERVAL 5 - WEEKDAY(CURDATE()) DAY) date
            UNION ALL
        SELECT DATE_ADD(CURDATE(), INTERVAL 6 - WEEKDAY(CURDATE()) DAY) date
) a
left join
(
select count(*) sum ,DATE_FORMAT(Create_time, '%Y-%m-%d' ) AS date from sys_user
WHERE DATE_FORMAT( Create_time, '%Y%m') = DATE_FORMAT( CURDATE(), '%Y%m' )
group by DATE_FORMAT(Create_time, '%Y%m%d')
) b
on a.date =b.date

 

 

2、统计本月

统计本月的sql

-- 本月
select a.date,IFNULL(b.sum, 0) sum 
from  ( 
    select date from (
    SELECT DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as date
    FROM ( 
            SELECT @xi:=@xi+1 as xc from 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  
            (SELECT @xi:=0) xc0 
    ) xcxc) x0 where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day))
) a 
left join 
(
    select count(*) sum ,DATE_FORMAT(Create_time, '%Y-%m-%d' ) AS date from sys_user 
    group by DATE_FORMAT(Create_time, '%Y%m%d') 
) b
on a.date =b.date order by date 

统计本月的结果

 

 

 

3、统计本年

统计本年的sql

-- 本年
select a.date,IFNULL(b.sum, 0) sum 
from  (  
            select DATE_FORMAT(adddate(DATE_SUB(CURDATE(), INTERVAL dayofyear(now()) - 1 DAY),
            INTERVAL numlist.id - 1 month), '%Y-%m') as date
            from (SELECT @xi := @xi + 1 as id
            from (SELECT 1 UNION SELECT 2 UNION SELECT 3) xc1,
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
            (SELECT @xi := 0) xc0) as numlist 
    ) a
left join
(
    SELECT count(*) sum, DATE_FORMAT(create_time, '%Y-%m') as date
    FROM sys_user WHERE YEAR(create_time) = YEAR(NOW()) GROUP BY date ORDER BY date
) b 
on a.date = b.date order by date 

 

统计本年的结果