解决MySQL子查询排序失效的问题
问题描述:高版本的MySQL(5.6以上)在子查询中使用order by 语句后查询结果并不会显示排序后的结果。
以下是我使用的两个解决方法,供参考:
1.当子查询的order by语句后面没有limit关键字时,数据库会自动优化,即忽略order by语句。因此只需要添加limit关键字即可。
2.在子查询中使用聚合函数。
具体示例如下所示: 现在我有一张表message,存放用户的对话信息,表中内容如下所示(图中我的一些测试数据请忽略:))
现在我想查询按照conversation_id分组后,每组的created_date值最大的数据项所组成的所有数据结果。查询结果的实际意义就是每组对话中最新的一条对话信息。预期查询结果应该显示id为2和10所在的两条数据。
先直接在子查询中使用order by语句,查询语句为:
select * from (select * from message order by created_date desc) b group by conversation_id;
查询结果为:
可以发现查询的结果与预期刚好相反,数据库默认查询每组created_date值最小的那一个,即最老的一个对话信息。
**1.**使用方法1解决:
查询语句:
select * from (select * from message order by created_date desc limit 9999) b group by conversation_id;
子查询中增加了限制查询9999条的语句,这样数据库就不会自动优化order by语句,但是要保证限制的数据要大于操作的数据的总数量。
查询结果:
此时查询结果与预期相同。
**2.**使用方法2解决:
select * from message ,
( select max(created_date) as date from message group by conversation_id) b
where b.date = message.created_date order by created_date desc;
在子查询中先使用聚合函数max找到每组created_date的最大值,然后通过where后的语句获取message中max(created_date)所在的数据项,最后再通过时间由大到小倒序排序即可。
查询结果:(下图截错了,仅展示的是没有对查询结果进行倒序排序的结果)
花了一下午回顾了一下数据库书,想到了第二个方法,同时也发现好多新大陆。充分说明之前学的太不扎实了,靠死记了一些,不用了之后就基本全忘了。还是自己动手能力差了些,不会学以致用。现在在努力。