mysql 5.7.15 union order by 子查询排序不生效

最新推荐文章于 2022-12-10 21:21:35 发布

微辣米粉

最新推荐文章于 2022-12-10 21:21:35 发布

阅读量823

点赞数

分类专栏：【数据库】

【数据库】专栏收录该内容

23 篇文章 1 订阅

订阅专栏

其实这么写理论上是对的，很多博客教程都是这样子写的。。

   select * from (SELECT * FROM article WHERE is_top=0 ORDER BY id DESC) as t1 
 
   union 
 
   select * from (SELECT * FROM article WHERE is_top=1 ORDER BY start_time desc) as t2

然后还是发现子查询的排序无效，尴尬，后来才发现如果order by 不带limit，会被优化器干掉，导致语句就是：

   select * from (SELECT * FROM article WHERE is_top=0 ) as t1 
 
   union 
 
   select * from (SELECT * FROM article WHERE is_top=1) as t2

解决方案：

   select * from (SELECT * FROM article WHERE is_top=0 ORDER BY id DESC limit 999999) as t1 
 
   union 
 
select * from (SELECT * FROM article WHERE is_top=1 ORDER BY start_time desc limit 999999) as t2

重点是limit!!!!!
转自:
http://blog.csdn.net/emaste_r/article/details/73550783