本文是小编为大家收集整理的关于postgresql，奇怪的OFFSET/LIMIT行为（记录顺序）。的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
基本上我有这个范围(SQL):
scope.to_sql => "SELECT \"offers\".* FROM \"offers\" INNER JOIN \"cities_offers\" ON \"offers\".\"id\" = \"cities_offers\".\"offer_id\" WHERE \"cities_offers\".\"city_id\" = 2 AND \"offers\".\"category_id\" IN (2) AND (offers.category_id is NOT NULL) ORDER BY offers.discount desc LIMIT 25 OFFSET 0"
以上查询的记录顺序有所不同，没有限制和偏移的相同查询:
scope[6] => #<Offer id: 8629 ... scope.except(:offset, :limit)[6] => #<Offer id: 8729 ...
8629和8729记录具有相同的discount值(属性i dord).
如果有可能在这种情况下保留相同的记录订购?
，您能提供建议吗?推荐答案
关系数据库基于设置，因此固有地无序；结果集中的记录顺序仅由子句的顺序指定 .如果两个行在顺序中通过子句中的表达式具有相同的值，则两次运行相同的查询可能会返回不同位置的这些行；通过添加限制和偏移来更改查询，只会通过使不同的订单更有可能使情况变得更糟.
如果您希望数据库以一定顺序为您提供行，则您必须通过子句中的顺序中完全指定订单.您必须在范围中添加更多order电话:
...order('offers.discount desc, offers.created_at asc')
或类似的东西，具体取决于您需要的特定顺序.
问题描述
so basically I have this scope(sql):
scope.to_sql => "SELECT \"offers\".* FROM \"offers\" INNER JOIN \"cities_offers\" ON \"offers\".\"id\" = \"cities_offers\".\"offer_id\" WHERE \"cities_offers\".\"city_id\" = 2 AND \"offers\".\"category_id\" IN (2) AND (offers.category_id is NOT NULL) ORDER BY offers.discount desc LIMIT 25 OFFSET 0"
Somehow the records order is different for the above query and the same one without LIMIT and OFFSET:
scope[6] => #<Offer id: 8629 ... scope.except(:offset, :limit)[6] => #<Offer id: 8729 ...
both 8629 and 8729 records have the same discount value ( the attribute I order by ).
Could you please advice if it's possible to keep the same records ordering under these circumstances?
推荐答案
Relational databases are set-based and hence inherently unordered; the order of the records in a result set is specified only by the ORDER BY clause. If two rows have the same values for the expression in the ORDER BY clause, then running the same query twice may return those rows in different positions; altering the query by adding LIMIT and OFFSET just makes things worse by making a different order more likely.
If you want the database to give you rows in a certain order, you must fully specify the order in your ORDER BY clause. You have to add more to the order call in your scope:
...order('offers.discount desc, offers.created_at asc')
or something like that depending on the specific order that you need.
更多相关问答
联系站长