PostgreSQL-从LIMIT OFFSET重复行 [英] PostgreSQL - repeating rows from LIMIT OFFSET

问题描述

我注意到在分页记录集中有一些重复的行.

I noticed some repeating rows in a paginated recordset.

当我运行此查询时:

SELECT "students".* 
FROM "students" 
ORDER BY "students"."status" asc 
LIMIT 3 OFFSET 0

我得到:

    | id | name  | status |
    | 1  | foo   | active |
    | 12 | alice | active |
    | 4  | bob   | active |

下一个查询:

SELECT "students".* 
FROM "students" 
ORDER BY "students"."status" asc 
LIMIT 3 OFFSET 3

我得到:

    | id | name  | status |
    | 1  | foo   | active |
    | 6  | cindy | active |
    | 2  | dylan | active |

为什么两个查询中都出现"foo"?

Why does "foo" appear in both queries?

推荐答案

为什么两个查询中都出现"foo"?

因为返回的所有行的status列都具有相同的值.在这种情况下，数据库可以自由以其想要的任何顺序返回行.

Because all rows that are returned have the same value for the status column. In that case the database is free to return the rows in any order it wants.

如果您想要可重复的订单，则需要在order by语句中添加第二列以使其一致.例如. ID列:

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:

SELECT students.* 
FROM students 
ORDER BY students.status asc, 
         students.id asc

如果状态行的两行值相同，则将按ID对其进行排序.

If two rows have the same value for the status column, they will be sorted by the id.

这篇关于PostgreSQL-从LIMIT OFFSET重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！