mysql根据日周月分组计算

1、按月份默认生成

(SELECt DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) date FROM ( SELECt @cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) FROM `mysql`.`help_topic`  LIMIT 14) a)

生成结果

2、按天默认生成

(select (curdate() + interval (cast(`mysql`.`help_topic`.`help_topic_id` as signed) - 10) day) AS date from `mysql`.`help_topic` having (date <= date_format(curdate(),'%Y-%m-%d')))

生成结果

1、获取原始数据

SELECt wgwl_date_event_volume,date_processing_capacity,date_unprocessed_volume,zjg_date_event_volume,date_case_volume, overView_time FROM task_status_statistics

获取结果

2、按日统计默认最近10天的数据

SELECt   SUM(b.zjg_date_event_volume)zjg_date_event_volume,SUM(b.date_case_volume)date_case_volume,temp.date overView_time
FROM
     (select (curdate() + interval (cast(`mysql`.`help_topic`.`help_topic_id` as signed) - 10) day) AS date from `mysql`.`help_topic` having (date <= date_format(curdate(),'%Y-%m-%d'))) temp
LEFT JOIN (select zjg_date_event_volume,date_case_volume, overView_time from task_status_statistics where overview_time >=DATE_SUB(CURDATE(), INTERVAL 10  DAY))
      b on temp.date = DATE_FORMAT(b.overview_time,'%Y-%m-%d')
GROUP BY temp.date ORDER BY temp.date DESC LIMIT 10

统计结果

3、按周统计默认最近10个周的数据

SELECt
       SUM(b.zjg_date_event_volume) zjg_date_event_volume,SUM(b.date_case_volume) date_case_volume,CONCAt(SUBSTr(DATE_FORMAT(temp.date,'%Y-%u') FROM 1 FOR 4),'年第',SUBSTr(DATE_FORMAT(temp.date,'%Y-%u'),6),'周') weeks,temp.date overView_time
FROM
       (select (curdate() + interval (cast(`mysql`.`help_topic`.`help_topic_id` as signed) - 100) day) AS date from `mysql`.`help_topic` having (date <= date_format(curdate(),'%Y-%m-%d'))) temp
LEFT JOIN (select zjg_date_event_volume,date_case_volume, overView_time from task_status_statistics where overview_time >=DATE_SUB(CURDATE(), INTERVAL 76  DAY))
        b on temp.date = DATE_FORMAT(b.overview_time,'%Y-%m-%d')
GROUP BY date_format(temp.date, '%Y-%u') ORDER BY temp.date DESC LIMIT 10

统计结果

4、按月统计默认最近10个月的数据

SELECt 
    SUM(b.zjg_date_event_volume) zjg_date_event_volume,SUM(b.date_case_volume) date_case_volume,aa.date overView_time
FROM
    (SELECt DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) date FROM ( SELECt @cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) FROM `mysql`.`help_topic`  LIMIT 14) a) aa 
LEFT JOIN (SELECt zjg_date_event_volume,date_case_volume,mon overView_time FROM (SELECt zjg_date_event_volume,date_case_volume,overView_time, DATE_FORMAT(overView_time,'%Y-%m') mon FROM task_status_statistics WHERe overView_time >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)) a) b on aa.date = b.overView_time
GROUP BY aa.date order by aa.date desc LIMIT 10

统计结果