问题描述:高版本的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;
在子查询中先使用聚合函数max找到每组created_date的最大值,然后通过where后的语句获取message中max(created_date)所在的数据项。
查询结果:
注:方法2查询的结果顺序实现和方法1一样,只需要再在语句后添加“ order by created_date desc”即可。
花了一下午回顾了一下数据库书,想到了第二个方法,同时也发现好多新大陆。充分说明之前学的太不扎实了,靠死记了一些,不用了之后就基本全忘了。还是自己动手能力差了些,不会学以致用。现在在努力。