MySQL select按季度和年同时Group By带来的问题以及解决方法

先看出问题的查询SQL

SELECT
	ANY_VALUE ( CONCAT(s.date_year,'年',s.date_quarter,'季度') ) AS time,
	SUM( s.order_amount ) AS order_amount,
	SUM( s.total_weight ) AS total_weight 
FROM
	(
SELECT
	somd.sales_number,
	som.order_amount,
	somd.total_weight,
	DATE_FORMAT( som.create_time, "%Y-%m-%d" ) AS create_time,
	t.date_year,
	t.date_month,
	t.date_quarter
FROM
	( SELECT sales_number, SUM( total_weight ) AS total_weight FROM sales_order_manage_details GROUP BY sales_number ) AS somd
	LEFT JOIN sales_order_manage som ON som.sales_number = somd.sales_number
	LEFT JOIN tcalendar t ON DATE_FORMAT( som.create_time, "%Y-%m-%d" ) = t.date_desc 
	) AS s 
WHERE
	s.create_time BETWEEN '2021-02-01'
	
	AND '2022-02-01'
	
GROUP BY
	s.date_quarter

查询的结果：

看查询时间，在2022年1月是有数据的，是属于第1季度，这里sql把数据合到2021年第1季度里面去了。

解决方法，将季度和年先拼接在一起，

CONCAT(t.date_year,'年',t.date_quarter,'季度') as quar

再进行按 “年+季度”分组Group By

SELECT
	s.quar as time,
	SUM( s.order_amount ) AS order_amount,
	SUM( s.total_weight ) AS total_weight 
FROM
	(
SELECT
	somd.sales_number,
	som.order_amount,
	somd.total_weight,
	DATE_FORMAT( som.create_time, "%Y-%m-%d" ) AS create_time,
	t.date_year,
	t.date_month,
	t.date_quarter,
	CONCAT(t.date_year,'年',t.date_quarter,'季度') as quar
FROM
	( SELECT sales_number, SUM( total_weight ) AS total_weight FROM sales_order_manage_details GROUP BY sales_number ) AS somd
	LEFT JOIN sales_order_manage som ON som.sales_number = somd.sales_number
	LEFT JOIN tcalendar t ON DATE_FORMAT( som.create_time, "%Y-%m-%d" ) = t.date_desc 
	) AS s 
WHERE
	s.create_time BETWEEN '2021-02-01'
	
	AND '2022-02-01'
	
GROUP BY
	s.quar

查询结果：