关于mysql:mysql-把date类型数据查出来后按周按月分组

本来的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 

后果如下：

拓展：
如果想要依照天、周、月等不同的粒度对数据进行分组统计也能够参考如下的语法：

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;