mysql按时间分组不对_MySQL按照不同时间分组

MySQL格式化日期： DATE_FORMAT(date, format)

date：时间字段     format：日期格式

根据format字符串格式化date值:

%S, %s 两位数字形式的秒( 00,01, ..., 59)%I, %i 两位数字形式的分( 00,01, ..., 59)%H 两位数字形式的小时，24 小时(00,01, ..., 23)%h 两位数字形式的小时，12 小时(01,02, ..., 12)%k 数字形式的小时，24 小时(0,1, ..., 23)%l 数字形式的小时，12 小时(1, 2, ..., 12)%T 24小时的时间形式(hh:mm:ss)%r 12小时的时间形式(hh:mm:ss AM 或hh:mm:ss PM)%p AM或PM%W 一周中每一天的名称(Sunday, Monday, ..., Saturday)%a 一周中每一天名称的缩写(Sun, Mon, ..., Sat)%d 两位数字表示月中的天数(00, 01,..., 31)%e 数字形式表示月中的天数(1, 2， ..., 31)%D 英文后缀表示月中的天数(1st, 2nd, 3rd,...)%w 以数字形式表示周中的天数( 0 = Sunday, 1=Monday, ..., 6=Saturday)%j 以三位数字表示年中的天数( 001, 002, ..., 366)%U 周(0, 1, 52)，其中Sunday 为周中的第一天%u 周(0, 1, 52)，其中Monday 为周中的第一天%M 月名(January, February, ..., December)%b 缩写的月名( January, February,...., December)%m 两位数字表示的月份(01, 02, ..., 12)%c 数字表示的月份(1, 2, ...., 12)%Y 四位数字表示的年份%y 两位数字表示的年份%% 直接值“%”

1、按照月份：

select sum(total_amount) as total, date_format(stat_date, '%Y-%m')  from week_report WHERE `stat_date` BETWEEN '2016-11-02' AND '2017-04-30' group by date_format(stat_date, '%Y-%m');

select sum(total_amount) as total,date_format(stat_date, '%Y-%m')   from week_report WHERE `stat_date` BETWEEN '2016-12-11' AND '2016-12-22' group by date_format(stat_date, '%Y-%m');

获得按照月份分组进行汇总的数据。

concat()连接字符串

-- month

select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%m')) months ,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx

WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by months;

-- 季度

select CONCAT(YEAR(stat_date),'_',quarter(stat_date)) qu,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx

WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by qu;

-- 周

select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%U')) weeks,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx

WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by weeks;

-- 天

select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%m'),'_',DATE_FORMAT(stat_date,'%d')) days, sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx

WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by days;

按年汇总，统计：

select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y');

按月汇总，统计：

select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y-%m');

按季度汇总，统计：

select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

按小时：

select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, '%Y-%m-%d %H ');

查询 本年度的数据:

SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())

查询数据附带季度数:

SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;

查询 本季度的数据:

SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());

本月统计:

select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())

本周统计:

select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())

N天内记录:

WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N