mysql 人工置顶 随机_Mysql 中实现文章置顶分页

先说一下背景吧：现在有一张表learn_article，里面有一个字段hot，当hot值为１时表示文章置顶，现在要分页查询文章列表，要求置顶文章放在最前面然后按id倒序排列，其他文章按id倒序排列。

提到置顶还获取列表，我当然想到使用UNION啦，于是就写出了下面这个SQL：

SELECT id,hot FROM

((SELECT * FROM learn_article WHERE hot = 1 ORDER BY id DESC)

UNION

(SELECT * FROM learn_article WHERE hot!= 1 ORDER BY id DESC))A

LIMIT 1,10

错误的置顶分页 1

然而，从结果也可以看出它不是我想要的结果，为毛它没有执行ORDER BY id DESC啊!查阅了UNION相关说明才知道：

联合查询不仅仅是将数据集合合并，他并不是将每个子查询一个一个查询出来后联接在一起，数据库是将整段查询语句解释之后统一查询得到的是整个的数据集合。另外order by在一个数据集合查询里也只能被执行一次并且要放在最后一个查询子句后，所以上面查询子句中的ORDER BY id DESC会被Mysql忽略！

因此，在联合查询里，order by 要写在最后一个子查询之后，并且，该排序是对整个联合查询出来的结果集排序的，并不是只对最后一个子查询排序，像下面这样：

SELECT id,hot FROM learn_article WHERE hot = 1

UNION

SELECT id,hot FROM learn_article WHERE hot!= 1

ORDER BY id DESC LIMIT 1,10

错误的置顶分页 2

但是，这更不是我要的结果啊！！到底还要我怎样呢！后来查阅资料发现：在使用UNION连接多个查询结果的情况下，想要在每个select子句使用ORDER BY 就需要同时使用limit！这样的话问题好像就能解决啦：

SELECT id,hot FROM (

(SELECT * FROM learn_article WHERE hot = 1 ORDER BY id DESC LIMIT 0,3)

UNION

(SELECT * FROM learn_article ORDER BY id DESC LIMIT 0,10))A

正确但麻烦的置顶分页查询

但是问题来啦，我怎么知道置顶的文章查几条，非置顶的文章查几条呢？那只有再查询一下置顶文章的条数啦：

SELECT COUNT(*) FROM learn_article WHERE hot =1

然后再根据查询起点offset和查询条数limit，计算置顶文章查询的数量(tlimit)和非置顶文章的查询起点(loffset)和查询数量(flimit):

int offset = Integer.valueOf(request.getParameter("offset"));

int limit = Integer.valueOf(request.getParameter("limit"));

//查询置顶文章数量，置顶文章不超过一页

int topNum = this.articleService.countTop(params);

int tlimit = topNum ;

int foffset = offset;

int flimit = limit;

if(topNum < offset){

//第二页以后

offset-=topNum;

tlimit =0;

}else{

//第一页

flimit = limit - topNum > 0 ? limit - topNum : 0;

tlimit = topNum;

}

然后就将tlimit,foffset,flimit传到后台，进行查询:

SELECT id,hot FROM (

(SELECT * FROM learn_article WHERE hot = 1 ORDER BY id DESC LIMIT 0,#{tlimit})

UNION

(SELECT * FROM learn_article ORDER BY id DESC LIMIT #{foffset},#{flimit}))A

这样就是实现了文章置顶的分页查询，是在太麻烦啦，到是有一种操作简单的方法就是使用FIND_IN_SET：

SELECT * FROM learn_article ORDER BY FIND_IN_SET(hot,'1') DESC ,id DESC LIMIT 0,10;

使用FIND_IN_SET实现置顶分页

但是呢，大家都知道的FIND_IN_SET执行效率太低啦，而且费内存！大家要是有更好的办法一定要告诉我哦！