下面语句主要用于根据数据库字段进行按天、按月、按年分组统计时的查询,仅做个人收藏备用。
1、如果时间字段类型为date或datetime:
- //按周查询
- SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
- //按天查询
- SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
- //按月查询
- SELECT DATE_FORMAT(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months
2、如果时间字段为时间戳(timestamp)或int表示的时间戳:
- //按周查询
- SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
- //按天查询
- SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
- //按月查询
- SELECT FROM_UNIXTIME(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months