首页 / MYSQL / MySQL组内排序取最大值
MySQL组内排序取最大值
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL组内排序取最大值,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4875字,纯文字阅读大概需要7分钟。
内容图文
--建表 create table testorder (id int not null, no int not null, name char(10) not null, primary key(id) )engine=innodb; --写入数据 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 | +----+----+-------+ --查询3 select * from (select id,no,name from testorder order by no desc)a group by a.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。
这里我们看出不同版本的返回值是不同的,先搁置数据量的变化引起执行结果不同的讨论,因为数据量大小很难测试。
官方文档
对上面的测试结果,在官方文档上,有如下的参考
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这个SQL_MODE出在mysql5.6(mariadb 10.0)时被引入,但本文讨论的内容和它无关,具体可以自己查看文档,这里不做讨论。在5.6,5.5的官方文档有相同的内容,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返回的行是无序的
因此,业务获得的正确的返回值也是误打误撞。
解决办法
那么这个问题该怎么解决?
在网上有一些SQL,很明显不满足需求,在这里做一下展示,希望同学们避免被误导:
错误SQL集合
select id,sbustring(GROUP_CONCAT(distinct no order by no desc separator ‘‘),‘‘,1),name from testorder group by name;
--通过添加索引来影响返回的结果集顺序 alter table testorder add index idx_no_name(no desc, name); --结果证明即使如此,desc也不会被正确执行;
--我司程序员的写法 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)
MySQL组内排序取最大值
标签:innodb insert 需要 use server 问题 add har str
本文系统来源:http://www.cnblogs.com/wyeat/p/7452942.html
内容总结
以上是互联网集市为您收集整理的MySQL组内排序取最大值全部内容,希望文章能够帮你解决MySQL组内排序取最大值所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。