format参数的取值为’%y%m%d’,可以按天输出统计结果。
第一种:统计某天总数
SELECT DATE_FORMAT(create_time,'%y年%m月%d日') as d,count(*)
FROM table
GROUP BY DATE_FORMAT(create_time,'%y%m%d')
ORDER BY d asc;
第二种;统计某列数据之和
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as d,sum(count)-sum(repeat_count)
FROM table
GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')
ORDER BY d asc;
第三:统计近七天数据(不包括今天,之前的七天)
select DATE_FORMAT(create_time, '%Y-%m-%d') time,sum(count) count,country from log where create_time >=curdate() - INTERVAL 7 DAY and create_time < curdate()
第四:统计近七天数据(包括今天推算七天)
select DATE_FORMAT(create_time, '%Y-%m-%d') time,sum(count) count from log where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(create_time) GROUP BY time;