mysql5.7 分组排序_MySQL 5.6 5.7 中组内排序的区别 · Yifans_Z Blog

MySQL 5.7 对比 5.6 有很多的变化。一个常见的需求：按条件分组后，取出每组中某字段最大值的那条记录。其实就是组内排序的问题，我的做法是：子查询先进行倒序排序，外层查询分组。

示例+----+----+-------+

| id | no | name |

+----+----+-------+

| 5 | 5 | Mike |

| 4 | 4 | Herry |

| 3 | 3 | wyett |

| 2 | 2 | John |

| 7 | 2 | John |

| 1 | 1 | Mike |

| 6 | 1 | John |

| 8 | 1 | Mike |

| 9 | 1 | Mike |

+----+----+-------+

要求：取出每人(按 name)，最大 no 的记录select * from (

select id,no,name from testorder order by no desc

)a group by a.name;+----+----+-------+

| id | no | name |

+----+----+-------+

| 4 | 4 | Herry |

| 2 | 2 | John |

| 5 | 5 | Mike |

| 3 | 3 | wyett |

+----+----+-------+

但是在 5.7 中，首先需要关闭 ql_mode = ONLY_FULL_GROUP_BY；相同的 name 值，返回则是取了 最早写入的数据行 ，忽略了 order by no desc，按照数据的逻辑存储顺序来返回+----+----+-------+

| id | no | name |

+----+----+-------+

| 4 | 4 | Herry |

| 2 | 2 | John |

| 1 | 1 | Mike |

| 3 | 3 | wyett |

+----+----+-------+

等价于select id,no,name from testorder group by name

A query such asSELECT field1, field2 FROM ( SELECT field1, field2 FROM table1 ORDER BY field2 ) alias

returns a result set that is not necessarily ordered by field2. This is not a bug.

A “table” (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows.

Rows in a table (or in a subquery in the FROM clause) do not come in any specific order.

可以总结为在 FROM 后的 subquery 中的 ORDER BY 会被忽略

GROUP BY cloumn 返回的行是无序的

解决方案select a.id,a.no,a.name

from testorder a inner join (

select max(no) no,name from testorder group by name

) b on a.no = b.no and a.name = b.name

group by name,no

小结

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

在标准 SQL 中，包含 GROUP BY 子句的查询 不能引用 select 列表中未在 GROUP BY 子句中命名的列。

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL.

MySQL 扩展了 GROUP BY 的标准 SQL 使用，以便选择列表可以引用 GROUP BY 子句中未命名的非集合列。这意味着前面的查询在 MySQL 中是合法的。

However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

但是，主要是在 GROUP BY 中 未命名的每个非分组列中的所有值对于每个组是相同的，这是有用的。服务器可以自由选择每个组中的任何值，因此除非它们相同，所选择的值是 不确定的。

Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

此外，通过添加 ORDER BY 子句不会影响来自每个组的值的选择。结果集排序发生在选择值后，ORDER BY 不影响 服务选择的每个组中的哪些值。

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.)

MySQL 5.7.5 及以上功能依赖检测功能。如果启用了 ONLY_FULL_GROUP_BY SQL 模式(默认情况下)，MySQL 将拒绝对列表，HAVING 条件或 ORDER BY 列表的查询引用在 GROUP BY 子句中既未命名的非集合列，也不在功能上依赖于它们。(5.7.5 之前，MySQL 没有检测到功能依赖关系，默认情况下不启用 ONLY_FULL_GROUP_BY)

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

你可以通过使用 ANY_VALUE() 使禁用了 ONLY_FULL_GROUP_BY 的 SQL，来实现相同的效果来引用非聚合列。