mysql 把date类型数据查出来后按周，按月分组 mysql

原本的SQL和查出的结果如下：

SELECT biz_day, record.amount FROM ods_sale_pay_record record WHERE project.delete_flag = 'N' AND ( record.biz_day >= '2021-10-01' AND record.biz_day <= '2021-10-31' OR record.biz_day >= '2022-01-01' AND record.biz_day <= '2022-01-31')

结果：

文章图片

现要求按照月份为维度汇总数据，可以使用DATE_FORMAT函数
修改后的SQL如下：

SELECT DATE_FORMAT(biz_day,'%Y%m') months, SUM( record.amount ) 'amount' FROM ods_sale_pay_record record WHERE record.status_code = 'PAY_RECORD_PAID' AND ( record.biz_day >= '2021-10-01' AND record.biz_day <= '2021-10-31' OR record.biz_day >= '2022-01-01' AND record.biz_day <= '2022-01-31') GROUP BY record.biz_day

结果如下：

文章图片

【mysql 把date类型数据查出来后按周，按月分组】拓展：
如果想要按照天、周、月等不同的粒度对数据进行分组统计也可以参考如下的语法：

1）按天统计：select DATE_FORMAT(biz_day,'%Y%m%d') days,SUM( record.amount ) 'amount' from test group by biz_day; 2）按周统计：select DATE_FORMAT(biz_day,'%Y%u') weeks,SUM( record.amount ) 'amount' from test group by biz_day; 3）按月统计:select DATE_FORMAT(biz_day,'%Y%m') months,SUM( record.amount ) 'amount' from test group by biz_day;