使用order by和limit出现奇怪的结果

Question

我正在尝试使用SQL设置分页。我希望每页显示3个结果，下面是我所做的：

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

我会很乐意为您翻译。以下是您需要翻译的内容：

我检查了很多次，这并不是非常复杂，但我的结果并不是我预期的：

第一页：

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

第二页:

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

第三页：

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

第四页：

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

正如您所看到的，第2页、第3页和第4页的结果是相同的...当我一次性获取这4页时：

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 |
+-----+-------------+------+

也许我漏掉了什么，或者排序结果和使用limit/offset不兼容，我不知道哪里出了问题。

- groseb

---

您观察到的行为在规范范围内。您缺少的是ORDER BY子句中的第二个表达式，以使顺序具有确定性。这些行确实按hits排序，但是有多行具有相同的hits值。MySQL可以自由地以任何顺序返回具有匹配hits值的行。为了以确定的顺序返回这些行，请在ORDER BY子句中添加一些表达式，这些表达式的组合对于行是唯一的。看起来id列可能是唯一的。例如**... ORDER BY hits DESC，id DESC LIMIT ...**。 - spencer7593

这个回答解决了你的问题吗？Postgres中当对两列具有相同值的数据进行排序时出现奇怪的排序错误（是否为错误？） - philipxy

Answer 1

问题在于所有行的点击次数都是1，因此在使用“ORDER BY hits”时它们的位置是不确定的。而且由于每次访问页面时都会执行新的查询，所以行将被重新“混淆”。

为了保持页面的一致性，您也可以按照它们的ID排序：

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

- Timo D

Answer 2

LIMIT基本上是在找到足够的匹配行后“中止”查询。例如，如果您有LIMIT 2，那么一旦找到与join/where子句匹配的两行，查询的其余部分就会消失，您将得到这两行。

但是，如果您在其中有一个ORDER BY，则整个匹配结果集将被排序，然后将该限制应用于排序后的结果集。

例如，如果您有一些记录将返回为5,10,203,3,92，则

SELECT id ... LIMIT 2             ->    (5,10),203,3,92
                                        ^^^^^^---actual returned-to-client results
SELECT id ... ORDER BY id LIMIT 2 ->    3,5,10,92,203 -> (3,5),10,92,203
                                           ^^---internal-only results
                                                         ^^^^--actual returned-to-client results.

- Marc B

Answer 3

不要使用

Limit 3 offset 0
Limit 3 offset 3
Limit 3 offset 6
Limit 3 offset 9

你应该能够使用

Limit 0,3
Limit 3,3
Limit 6,3
Limit 9,3

同样的效果

- Beyayfivay

---

这并不是对所提出问题的真正答案。 - Mike G

是的，这是“LIMIT”子句的替代语法，但这并不能解释 OP 所观察到的行为。而且进行此更改不会影响返回的行。 - spencer7593