PostgreSQL - 从 LIMIT OFFSET 重复行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13580826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - repeating rows from LIMIT OFFSET
提问by keewooi
I noticed some repeating rows in a paginated recordset.
我注意到分页记录集中有一些重复的行。
When I run this query:
当我运行此查询时：
SELECT "students".*
FROM "students"
ORDER BY "students"."status" asc
LIMIT 3 OFFSET 0
I get:
我得到：
| id | name | status |
| 1 | foo | active |
| 12 | alice | active |
| 4 | bob | active |
Next query:
下一个查询：
SELECT "students".*
FROM "students"
ORDER BY "students"."status" asc
LIMIT 3 OFFSET 3
I get:
我得到：
| id | name | status |
| 1 | foo | active |
| 6 | cindy | active |
| 2 | dylan | active |
Why does "foo" appear in both queries?
为什么“foo”出现在两个查询中？
回答by a_horse_with_no_name
Why does "foo" appear in both queries?
为什么“foo”出现在两个查询中？
Because all rows that are returned have the same value for the statuscolumn. In that case the database is free to return the rows in any order it wants.
因为返回的所有行都具有相同的status列值。在这种情况下，数据库可以自由地以它想要的任何顺序返回行。
If you want a reproducable ordering you need to add a second column to your order by statement to make it consistent. E.g. the ID column:
如果您想要可重复的排序，则需要在 order by 语句中添加第二列以使其一致。例如 ID 列：
SELECT students.*
FROM students
ORDER BY students.status asc,
students.id asc
If two rows have the same value for the status column, they will be sorted by the id.
如果两行的状态列具有相同的值，它们将按 id 排序。
回答by Ahmed MANSOUR
For more details from PostgreSQL documentation (http://www.postgresql.org/docs/8.3/static/queries-limit.html) :
有关 PostgreSQL 文档 ( http://www.postgresql.org/docs/8.3/static/queries-limit.html) 的更多详细信息：
When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows.You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.
使用 LIMIT 时，使用 ORDER BY 子句将结果行限制为唯一的 order 很重要。否则，您将获得不可预测的查询行子集。您可能会要求第十行到第二十行，但第十行到第二十行的顺序是什么？顺序未知，除非您指定 ORDER BY。
The query optimizer takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.
查询优化器在生成查询计划时会考虑 LIMIT，因此根据您为 LIMIT 和 OFFSET 提供的内容，您很可能会得到不同的计划（产生不同的行顺序）。因此，除非您使用 ORDER BY 强制执行可预测的结果排序，否则使用不同的 LIMIT/OFFSET 值来选择查询结果的不同子集会产生不一致的结果。这不是错误；这是 SQL 不承诺以任何特定顺序传递查询结果的事实的内在结果，除非使用 ORDER BY 来限制顺序。
