SQL如何实现组内排序取最大值

2025-03-15 技术教程

这篇文章主要介绍了SQL如何实现组内排序取最大值，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

测试用例

--建表createtabletestorder(idintnotnull,nointnotnull,namechar(10)notnull,primarykey(id))engine=innodb;--写入数据insertintotestordervalues(1,1,'Mike'),(2,2,'John'),(3,3,'wyett'),(4,4,'Herry'),(5,5,'Mike'),(6,1,'John'),(7,2,'John'),(8,1,'Mike'),(9,1,'Mike');--查询1select*fromtestorder;+----+----+-------+|id|no|name|+----+----+-------+|1|1|Mike||2|2|John||3|3|wyett||4|4|Herry||5|5|Mike||6|1|John||7|2|John||8|1|Mike||9|1|Mike|+----+----+-------+--查询2select*fromtestorderorderbynodesc;+----+----+-------+|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|+----+----+-------+--查询3select*from(selectid,no,namefromtestorderorderbynodesc)agroupbya.name;

查询3这条SQL是我们需要讨论的内容，也是业务线为实现组内排序取最大值所采用的SQL。标准的程序员反馈问题方式：XXX时间点之前查询时正常的，这之后突然就不正常了，你们DBA是不是做什么改动了？我把数据恢复到自己的测试机，返回值也是正常的。暂且不去管姿势是否正确，对这条SQL的分析，我们其实可以看出：（1）程序员期待group by执行结果是按照临时表a的数据顺序来取值；（2）程序员未考虑版本因素，数据量变化的因素；为此，我构建了上面的测试用例。

测试

在不同版本的MySQL来进行测试：发现在Percona 5.5，Percona 5.1，MySQL 5.6关闭sql_mode= ONLY_FULL_GROUP_BY，MySQL5.1等版本下，返回值确如程序员期待的顺序，按照order by no desc的顺序，相同name返回no值最大的数据；

+----+----+-------+|id|no|name|+----+----+-------+|4|4|Herry||2|2|John||5|5|Mike||3|3|wyett|+----+----+-------+

在mysql5.7，关闭sql_mode= ONLY_FULL_GROUP_BY和mariadb 10.*版本中，相同的name值，返回则是取了最早写入的数据行，忽略了order by no desc，按照数据的逻辑存储顺序来返回；

+----+----+-------+|id|no|name|+----+----+-------+|4|4|Herry||2|2|John||1|1|Mike||3|3|wyett|+----+----+-------+

其实在这里，SQL等价于select id,no,name from testorder group by name。
这里我们看出不同版本的返回值是不同的，先搁置数据量的变化引起执行结果不同的讨论，因为数据量大小很难测试。

官方文档

对上面的测试结果，在官方文档上，有如下的参考

IfONLY_FULL_GROUP_BYisdisabled...Inthiscase,theserverisfreetochooseanyvaluefromeachgroup,sounlesstheyarethesame,thevalueschosenareindeterminate,whichisprobablynotwhatyouwant.Furthermore,theselectionofvaluesfromeachgroupcannotbeinfluencedbyaddinganORDERBYclause.Resultsetsortingoccursaftervalueshavebeenchosen,andORDERBYdoesnotaffectwhichvaluewithineachgrouptheserverchooses.

ONLY_FULL_GROUP_BY这个SQL_MODE出在mysql5.6(mariadb 10.0)时被引入，但本文讨论的内容和它无关，具体可以自己查看文档，这里不做讨论。在5.6,5.5的官方文档有相同的内容，Mariadb也有类似的解释

Ifyouselectanon-groupedcolumnoravaluecomputedfromanon-groupedcolumn,itisundefinedwhichrowthereturnedvalueistakenfrom.ThisisnotpermittediftheONLY_FULL_GROUP_BYSQL_MODEisused.

并且，对from后的subquery子表中的order by也给出了解释

AquerysuchasSELECTfield1,field2FROM(SELECTfield1,field2FROMtable1ORDERBYfield2)aliasreturnsaresultsetthatisnotnecessarilyorderedbyfield2.Thisisnotabug.A"table"(andsubqueryintheFROMclausetoo)is-accordingtotheSQLstandard-anunorderedsetofrows.Rowsinatable(orinasubqueryintheFROMclause)donotcomeinanyspecificorder.

好了，有了这些解释，问题很明朗：

在from 后的subquery中的order by会被忽略

group by cloumn返回的行是无序的

因此，业务获得的正确的返回值也是误打误撞。

解决办法

那么这个问题该怎么解决？

在网上有一些SQL，很明显不满足需求，在这里做一下展示，希望同学们避免被误导：

错误SQL集合

selectid,sbustring(GROUP_CONCAT(distinctnoorderbynodescseparator''),'',1),namefromtestordergroupbyname;

--通过添加索引来影响返回的结果集顺序altertabletestorderaddindexidx_no_name(nodesc,name);--结果证明即使如此，desc也不会被正确执行；

--我司程序员的写法select*from(selectid,no,namefromtestorderorderbynodesc)agroupbya.name

selectid,max(no),namefromtestordergroupbyname