mysql 分别按月、日、周进行分组查询

下面语句主要用于根据数据库字段进行按天、按月、按年分组统计时的查询，仅做个人收藏备用。

1、如果时间字段类型为date或datetime：

1. 按周查询
   SELECT 
   DATE_FORMAT(create_time,'%Y%u') weeks,
   COUNT(id) count 
   FROM  order
   where channel_code =#{channelCode}
   GROUP BY weeks
   order by count desc
2. 按天查询
   SELECT 
   DATE_FORMAT(created_at,'%Y-%m-%d') days,
   COUNT(id) count 
   FROM  order
   where channel_code =#{channelCode}
   GROUP BY days
   order by count desc
3. 按月查询
   SELECT 
   DATE_FORMAT(create_time,'%Y%m') months,
   COUNT(id) count 
   FROM  order
   where channel_code =#{channelCode}
   GROUP BY months
   order by count desc

2、如果时间字段为时间戳（timestamp）或int表示的时间戳：

1. 按周查询
   SELECT 
   FROM_UNIXTIME(create_time,'%Y%u') weeks,
   COUNT(id) count 
   FROM  order
   where channel_code =#{channelCode}
   GROUP BY weeks
   order by count desc
2. 按天查询
   SELECT 
   FROM_UNIXTIME(create_time,'%Y%m%d') days,
   COUNT(id) count 
   FROM  order
   where channel_code =#{channelCode}
   GROUP BY days
   order by count desc
3. 按月查询
   SELECT 
   FROM_UNIXTIME(create_time,'%Y%m') months,
   COUNT(id) count 
   FROM  order
   where channel_code =#{channelCode}
   GROUP BY months
   order by count desc