Press "Enter" to skip to content

MySQL GROUP BY读取数据问题

在写业务代码时,遇到GROUP BY取数据问题,记录如下。

MySQL 版本5.6

假设我们的表结构如下:

表:message

mes_idorder_codeuser_coderemark
1O123C1001测试
2O223C1002备注
3O123C9527测试22

现需要按order_code分组,取最大mes_id一条。

最开始的思路是先将messagemes_id倒序,然后再GROUP BY

SELECT
	* 
FROM
	( SELECT * FROM message ORDER BY mes_id DESC ) m1 
GROUP BY
	order_code;

结果并没有正确输出。没有使用到子查询的排序。

为啥呢?

原来MySQL5.7版本之前不参与grouping字段是不确定返回哪条数据的。

文档:

The select list extension also applies to ORDER BY. That is, you can refer to nonaggregated columns in the ORDER BY clause that do not appear in the GROUP BY clause. (However, as mentioned previously, ORDER BY does not affect which values are chosen from nonaggregated columns; it only sorts them after they have been chosen.) This extension does not apply if the ONLY_FULL_GROUP_BY SQL mode is enabled.

所以刚刚SQL可以改为:

SELECT
	* 
FROM
	( SELECT MAX( mes_id ) mes_id FROM message GROUP BY order_code ) m1
	LEFT JOIN message m ON m1.mes_id = m.mes_id 
ORDER BY
	m.mes_id DESC;

MySQL5.7之后版本,仍然是未定义状态,并且默认直接不允许未定义状态的grouping查询,会报错:

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by

具体看文档 ONLY_FULL_GROUP_BY

参考: