mysql中按照季度、月份等分组

mysql中关于分组的使用

select DATE_FORMAT(create_time,"%Y%u") weeks,count(caseid) count from tc_case group by weeks; 
select DATE_FORMAT(create_time,"%Y%m%d") days,count(caseid) count from tc_case group by days; 
select DATE_FORMAT(create_time,"%Y%m") months,count(caseid) count from tc_case group by months;

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;