MySQL GROUP BY读取数据问题

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

MySQL 版本5.6

假设我们的表结构如下:

表:message

mes_id order_code user_code remark 1 O123 C1001 测试 2 O223 C1002 备注 3 O123 C9527 测试22 现需要按order_code分组,取最大mes_id一条。

最开始的思路是先将message按mes_id倒序,然后再GROUP BY。

1
2
3
4
5
6
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可以改为:

1
2
3
4
5
6
7
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

参考:

updatedupdated2019-05-212019-05-21
Load Comments?