下面语句主要用于根据数据库字段进行按天、按月、按年分组统计时的查询,仅做个人收藏备用。
1、如果时间字段类型为date或datetime:
- 按周查询
SELECT
DATE_FORMAT(create_time,'%Y%u') weeks,
COUNT(id) count
FROM order
where channel_code =#{channelCode}
GROUP BY weeks
order by count desc - 按天查询
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 - 按月查询
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表示的时间戳:
- 按周查询
SELECT
FROM_UNIXTIME(create_time,'%Y%u') weeks,
COUNT(id) count
FROM order
where channel_code =#{channelCode}
GROUP BY weeks
order by count desc - 按天查询
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 - 按月查询
SELECT
FROM_UNIXTIME(create_time,'%Y%m') months,
COUNT(id) count
FROM order
where channel_code =#{channelCode}
GROUP BY months
order by count desc