SQLite:如何同时实现随机排序和分页? [英] SQLite: How to achive RANDOM ORDER and pageintation at the same time?

问题描述

我有一个电影表，我希望能够查询数据库并获得电影的随机列表，但我也不希望它返回所有可用的电影，所以我'm 使用 LIMIT 和 OFFSET.问题是当我做这样的事情时:

I have a table of movies, I want to be able to query the database and get a randomized list of movies, but also I don't want it to return all movies available so I'm using LIMIT and OFFSET. The problem is when I'm doing something like this:

SELECT *  FROM Movie ORDER BY RANDOM() LIMIT 50 OFFSET 0

然后当使用 LIMIT 50 OFFSET 50 查询下一页时，RANDOM 种子会发生变化，因此第一页中的行有可能包含在第二页中，这不是所需的行为.

and then when querying for the next page with LIMIT 50 OFFSET 50 the RANDOM seed changes and so it's possible for rows from the first page to be included in the second page, which is not the desired behavior.

如何实现随机顺序并通过页面保留它?据我所知，SQLite 的 RANDOM 函数不支持自定义种子.

How can I achieve a random order and preserve it through the pages? As far as I know SQLite doesn't support custom seed for it's RANDOM function.

谢谢！

推荐答案

您不能保留随机值.您必须在表中添加另一个字段名称以保持随机顺序

You cant preserve the random values. You have to add another field name to your table to keep the random order

UPDATE movie
SET randomOrder = Random();

然后你可以检索页面

SELECT *  
FROM Movie 
ORDER BY randomOrder 
LIMIT 50 OFFSET 0

这篇关于SQLite:如何同时实现随机排序和分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！