使用order by和limit进行结果奇怪

本文介绍了使用order by和limit进行结果奇怪的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用SQL设置分页.我希望每页3个结果，这是我已经完成的事情:

I'm trying to set up a pagination using SQL. I want 3 results per page and here is what I have done :

SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC LIMIT 3 OFFSET 0; --Page 1 SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC LIMIT 3 OFFSET 3; --Page 2 SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC LIMIT 3 OFFSET 6; --Page 3 SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC LIMIT 3 OFFSET 9; --Page 4

我检查了很多次，这不是很复杂，但是我的结果并不是我所期望的:

I checked many times and this is not very complicated but my results are not really what I expected :

第1页:

+-----+--------+------+ | id | mot | hits | +-----+--------+------+ | 2 | test | 46 | | 1 | blabla | 5 | | 475 | intro | 3 | +-----+--------+------+

第2页:

+-----+-------+------+ | id | mot | hits | +-----+-------+------+ | 478 | vrai | 1 | | 26 | ouest | 1 | | 27 | serie | 1 | +-----+-------+------+

第3页:

+-----+-------+------+ | id | mot | hits | +-----+-------+------+ | 27 | serie | 1 | | 26 | ouest | 1 | | 478 | vrai | 1 | +-----+-------+------+

第4页:

+-----+-------+------+ | id | mot | hits | +-----+-------+------+ | 27 | serie | 1 | | 26 | ouest | 1 | | 478 | vrai | 1 | +-----+-------+------+

如您所见，第2、3和4页的结果相同... 当我一次取回4页时:

As you can see, pages 2, 3 and 4 have the same results... When I fetch the 4 pages in one :

SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC LIMIT 20 OFFSET 0;

结果:

+-----+-------------+------+ | id | mot | hits | +-----+-------------+------+ | 2 | test | 46 | | 1 | blabla | 5 | | 475 | intro | 3 | | 35 | acteurs | 1 | | 36 | milieu | 1 | | 37 | industriel | 1 | | 38 | plaire | 1 | | 39 | grandes | 1 | | 40 | ingenieries | 1 | | 41 | francaises | 1 | | 34 | partenaire | 1 | | 33 | rthgyjhkj | 1 | | 32 | cool | 1 | | 31 | super | 1 | | 30 | vieux | 1 | | 29 | moteur | 1 | | 28 | yahoo | 1 | | 27 | serie | 1 | | 26 | ouest | 1 | | 478 | vrai | 1 | +-----+-------------+------+

也许我遗漏了一些东西或排序结果，并且使用极限/偏移量不兼容，我不知道出了什么问题.

Maybe I'm missing something or sorting results and using limit/offset are not compatible, I don't know what's wrong.

推荐答案

这里的问题是所有行的命中计数都为1，因此使用ORDER BY hits时它们的位置不确定.而且由于您每次访问页面时都会执行一个新查询，因此这些行将被重新加扰".

The problem here is that the rows all have a hit count of 1, therefore their position when using ORDER BY hits is non-deterministic. And since you execute a new query each time you access a page, the rows will be "scrambled" anew.

要保持页面的一致性，您还可以按其ID排序:

To keep your pages consistent, you could also order by their id:

SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC, id ASC LIMIT 3 OFFSET 0; --Page 1