MySQL 错误 'only_full_group_by': 解决 SELECT 语句中聚合函数和非聚合列问题

在使用 MySQL 进行数据查询时，有时会遇到 'only_full_group_by' 错误，特别是在 SELECT 语句中使用了聚合函数（如 SUM()），但没有使用 GROUP BY 子句对其他非聚合列进行分组。

例如以下 SQL 语句：

SELECT
	Material AS material,
	Batch AS batch,
	PostingDate AS postingDate,
	quantityInEntryUnit AS quantityInEntryUnit,
	( QuantityInEntryUnit - ArtificialConsume - AutomaticConsume ) AS availableInventory,
	ArtificialConsume AS artificialConsume,
	SUM( QuantityInEntryUnit - ArtificialConsume - AutomaticConsume ) AS totalQuantity,
	AutomaticConsume AS automaticConsume 
FROM
	vmi_materialdocument 
WHERE
	DATE_FORMAT( PostingDate, '%Y-%m' ) = DATE_FORMAT( '2023-09-01 08:00:00', '%Y-%m' ) 
ORDER BY
	PostingDate ASC

该语句报错：

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'etl_hg_prd_new.vmi_materialdocument.Material'; this is incompatible with sql_mode=only_full_group_by

这是因为在 SELECT 子句中使用了聚合函数 SUM()，但没有使用 GROUP BY 子句对其他非聚合列进行分组。

要解决这个问题，可以修改 SQL 语句，在 SELECT 子句中添加 GROUP BY 子句，对需要分组的列进行分组。

修改后的 SQL 语句如下：

SELECT
	Material AS material,
	Batch AS batch,
	PostingDate AS postingDate,
	quantityInEntryUnit AS quantityInEntryUnit,
	( QuantityInEntryUnit - ArtificialConsume - AutomaticConsume ) AS availableInventory,
	ArtificialConsume AS artificialConsume,
	SUM( QuantityInEntryUnit - ArtificialConsume - AutomaticConsume ) AS totalQuantity,
	AutomaticConsume AS automaticConsume 
FROM
	vmi_materialdocument 
WHERE
	DATE_FORMAT( PostingDate, '%Y-%m' ) = DATE_FORMAT( '2023-09-01 08:00:00', '%Y-%m' ) 
GROUP BY
	material, batch, postingDate, quantityInEntryUnit, artificialConsume, automaticConsume
ORDER BY
	PostingDate ASC

通过添加 GROUP BY 子句，对所有非聚合列进行分组，即可解决 'only_full_group_by' 错误，并得到正确的结果。