mysql組內(nèi)排序取最大值

最近業(yè)務(wù)反饋一個(gè)查詢異常的問題，需要DBA對查詢結(jié)果異常給出解釋，并幫助他們解決該問題。問題本質(zhì)是一個(gè)組內(nèi)排序取最大值的問題，根據(jù)業(yè)務(wù)需求，我構(gòu)建了測試用例

測試用例

--建表
create table testorder
(id int not null,
no int not null,
name char(10) not null,
primary key(id)
)engine=innodb;
--寫入數(shù)據(jù)
insert into testorder values (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');
--查詢1
select * from testorder;
+----+----+-------+
| 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  |
+----+----+-------+
--查詢2
select * from testorder order by no desc;
+----+----+-------+
| 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 (select id,no,name from testorder order by no desc)a group by a.name;

查詢3這條SQL是我們需要討論的內(nèi)容，也是業(yè)務(wù)線為實(shí)現(xiàn)組內(nèi)排序取最大值所采用的SQL。標(biāo)準(zhǔn)的程序員反饋問題方式：XXX時(shí)間點(diǎn)之前查詢時(shí)正常的，這之后突然就不正常了，你們DBA是不是做什么改動(dòng)了？我把數(shù)據(jù)恢復(fù)到自己的測試機(jī)，返回值也是正常的。暫且不去管姿勢是否正確，對這條SQL的分析，我們其實(shí)可以看出：（1）程序員期待group by執(zhí)行結(jié)果是按照臨時(shí)表a的數(shù)據(jù)順序來取值；（2）程序員未考慮版本因素，數(shù)據(jù)量變化的因素；為此，我構(gòu)建了上面的測試用例。

測試

在不同版本的MySQL來進(jìn)行測試：發(fā)現(xiàn)在Percona 5.5，Percona 5.1，MySQL 5.6關(guān)閉sql_mode= ONLY_FULL_GROUP_BY，MySQL5.1等版本下，返回值確如程序員期待的順序，按照order by no desc的順序，相同name返回no值最大的數(shù)據(jù)；

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

在mysql5.7，關(guān)閉sql_mode= ONLY_FULL_GROUP_BY和mariadb 10.*版本中，相同的name值，返回則是取了最早寫入的數(shù)據(jù)行，忽略了order by no desc，按照數(shù)據(jù)的邏輯存儲順序來返回；

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

其實(shí)在這里，SQL等價(jià)于select id,no,name from testorder group by name。
這里我們看出不同版本的返回值是不同的，先擱置數(shù)據(jù)量的變化引起執(zhí)行結(jié)果不同的討論，因?yàn)閿?shù)據(jù)量大小很難測試。

官方文檔

對上面的測試結(jié)果，在官方文檔上，有如下的參考

If ONLY_FULL_GROUP_BY is disabled...In this case, the server is free to choose any value from each group, 
so unless they are the same, the values chosen are indeterminate, which is probably not what you want. 
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 value within 
each group the server chooses.

ONLY_FULL_GROUP_BY這個(gè)SQL_MODE出在mysql5.6(mariadb 10.0)時(shí)被引入，但本文討論的內(nèi)容和它無關(guān)，具體可以自己查看文檔，這里不做討論。在5.6,5.5的官方文檔有相同的內(nèi)容，Mariadb也有類似的解釋

 If you select a non-grouped column or a value computed from a non-grouped column, it is undefined 
which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY SQL_MODE is used.

并且，對from后的subquery子表中的order by也給出了解釋

 A query such as

SELECT 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返回的行是無序的

因此，業(yè)務(wù)獲得的正確的返回值也是誤打誤撞。

解決辦法

那么這個(gè)問題該怎么解決？

在網(wǎng)上有一些SQL，很明顯不滿足需求，在這里做一下展示，希望同學(xué)們避免被誤導(dǎo)：

錯(cuò)誤SQL集合

select id,sbustring(GROUP_CONCAT(distinct no order by no desc separator ''),'',1),name from testorder group by name;

--通過添加索引來影響返回的結(jié)果集順序
alter table testorder add index idx_no_name(no desc, name);
--結(jié)果證明即使如此，desc也不會被正確執(zhí)行；

--我司程序員的寫法
select * from (select id,no,name from testorder order by no desc)a group by a.name

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

我們可以這樣寫,雖然效率不高

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

或者這樣

select a.id,a.no,a.name 
from testorder a 
group by a.name,a.no 
having a.no=(select max(no) from testorder where name=a.name)