MySql中按周、月统计数量
经需求要求对某个表进行按每周、每月统计报表显示,以下为sql
1、按周统计数据:
SELECT DATE_FORMAT( CreateDate, '%Y-%u' ) AS WEEK, DATE_FORMAT(date_sub( CreateDate, INTERVAL dayofweek( CreateDate )- 2 DAY ),'%Y-%m-%d') AS CreateDate, count(*) AS count FROM gyl_order GROUP BY WEEK;
查看效果:
日期字段为每周的周一
2、按月统计数据:
SELECT DATE_FORMAT( CreateDate, '%Y-%m' ) AS Month, DATE_FORMAT( CreateDate, '%Y-%m-01' ) AS CreateDate, count(*) AS count FROM gyl_order GROUP BY Month;
查看效果:
日期字段为每月的1号