其中qtime是日期格式的时间,如果字段为时间戳,可将qtime改为FROM_UNIXTIME(qtime,'%Y-%m-%d %H:%i:%s')
1、每年
select year(qtime) AS '年',
sum(Total) '销售合计'
from captureRecord
group by year(qtime)
2、每月
select year(qtime) '年',
month(qtime) '月',
sum(Total) '抓拍数量'
from captureRecord
group by year(qtime),
month(qtime)
3、每日
select year(qtime) '年',
month(qtime) '月',
day(qtime) '日',
sum(Total) '销售合计'
from captureRecord
group by year(qtime),
month(qtime),
day(ordertime)
4、另外每日也可以这样:
select convert(char(8),qtime,112) dt,
sum(Total) '销售合计'
from captureRecord
group by convert(char(8),qtime,112)
5、另外,每月(年、日)的记录条数
select year(qtime) '年',
month(qtime) '月',
count(*) '销售记录'
from captureRecord
group by year(qtime),
month(qtime)
6、每几小时段的记录条数 (qtime date时间) 使用div函数 hour(qtime) div 4 每四小时的数据
SELECT DATE_FORMAT(qtime,'%H') AS groupHour,hour(qtime) div 4 as timegroup,count(1) AS
captureCount from captureRecord group by timegroup