SQL 在 SQLite 中选择随机行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4114940/
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
Select random row(s) in SQLite
提问by Fahad Sadah
In MySQL, you can select X random rows with the following statement:
在 MySQL 中，您可以使用以下语句随机选择 X 行：
SELECT * FROM table ORDER BY RAND() LIMIT X
This does not, however, work in SQLite. Is there an equivalent?
但是，这在 SQLite 中不起作用。有等价物吗？
回答by Ali
For a much better performanceuse:
为了获得更好的性能，请使用：
SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)
SQL engines first load projected fields of rows to memory then sort them, here we just do a random sort on id field of each row which is in memory because it's indexed, then separate X of them, and find the whole row using these X ids.
SQL 引擎首先将行的投影字段加载到内存中，然后对它们进行排序，这里我们只是对内存中的每一行的 id 字段进行随机排序，因为它已被索引，然后将它们中的 X 分开，并使用这些 X id 找到整行.
So this consume less RAM and CPU as table grows!
因此，随着表的增长，这会消耗更少的 RAM 和 CPU！
回答by Donnie
SELECT * FROM table ORDER BY RANDOM() LIMIT X
SELECT * FROM table ORDER BY RANDOM() LIMIT X
回答by bkaid
SELECT * FROM table ORDER BY RANDOM() LIMIT 1
回答by Davor Josipovic
All answers here are based on ORDER BY. This is veryinefficient (i.e. unusable) for large sets because you will evaluate RANDOM()for each record, and then ORDER BYwhich is a resource expensive operation.
这里的所有答案都基于ORDER BY. 这对于大型集合来说非常低效（即无法使用），因为您将评估RANDOM()每条记录，然后ORDER BY这是一个资源昂贵的操作。
An other approach is to place abs(CAST(random() AS REAL))/9223372036854775808 < 0.5in the WHEREclause to get in this case for example 0.5 hit chance.
另一种方法是abs(CAST(random() AS REAL))/9223372036854775808 < 0.5在WHERE子句中放置在这种情况下获得例如 0.5 的命中机会。
SELECT *
FROM table
WHERE abs(CAST(random() AS REAL))/9223372036854775808 < 0.5
The large number is the maximum absolute number that random()can produce. The abs()is because it is signed. Result is a uniformly distributed random variable between 0 and 1.
大数是random()可以产生的最大绝对数。这abs()是因为它已签名。结果是一个介于 0 和 1 之间的均匀分布的随机变量。
This has its drawbacks. You can not guarantee a result and if the threshold is large compared to the table, the selected data will be skewed towards the start of the table. But in some carefully designed situations, it can be a feasible option.
这有其缺点。您无法保证结果，如果阈值与表格相比较大，则所选数据将偏向表格的开头。但在一些精心设计的情况下，它可能是一个可行的选择。
回答by Evhz
This one solves the negative RANDOM integers, and keeps good performance on large datasets:
这个解决了负 RANDOM 整数，并在大型数据集上保持良好的性能：
SELECT * FROM table LIMIT 1 OFFSET abs(random() % (select count(*) from table));
where:abs(random() % n )Gives you a positive integer in range(0,n)
其中：abs(random() % n )给你一个正整数range(0,n)
