我不知道这有多有效，但我以前用过:

SELECT TOP 1 * FROM MyTable ORDER BY newid()

因为guid是非常随机的，所以顺序意味着您得到的是随机行。

似乎列出的许多想法仍然使用排序

但是，如果使用临时表，则可以分配一个随机索引(就像许多解决方案所建议的那样)，然后获取第一个大于0到1之间任意数字的索引。

例如(对于DB2):

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY

我还没看出来答案有什么不同。我有一个额外的约束条件，给定一个初始种子，每次都要选择相同的行集。

对于MS SQL:

最小的例子:

select top 10 percent *
from table_name
order by rand(checksum(*))

规范化执行时间:1.00

NewId()例子:

select top 10 percent *
from table_name
order by newid()

规范化执行时间:1.02

NewId()比rand(checksum(*))慢不了多少，所以您可能不希望对大型记录集使用它。

初始种子选择:

declare @seed int
set @seed = Year(getdate()) * month(getdate()) /* any other initial seed here */

select top 10 percent *
from table_name
order by rand(checksum(*) % seed) /* any other math function here */

如果给定一个种子，你需要选择相同的集合，这似乎是可行的。

对于SQL Server 2005和2008，如果我们想要一个随机的个别行样本(来自Books Online):

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)

SQL Server

Newid ()/order by可以工作，但对于大型结果集来说代价非常高，因为它必须为每一行生成一个id，然后对它们进行排序。

从性能的角度来看，TABLESAMPLE()很好，但是您将得到结果的聚集(将返回页面上的所有行)。

为了获得更好的真实随机样本，最好的方法是随机过滤掉行。我在SQL Server Books Online文章使用TABLESAMPLE限制结果集中找到了以下代码示例:

If you really want a random sample of individual rows, modify your query to filter out rows randomly, instead of using TABLESAMPLE. For example, the following query uses the NEWID function to return approximately one percent of the rows of the Sales.SalesOrderDetail table: SELECT * FROM Sales.SalesOrderDetail WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) The SalesOrderID column is included in the CHECKSUM expression so that NEWID() evaluates once per row to achieve sampling on a per-row basis. The expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) evaluates to a random float value between 0 and 1.

当对一个有1,000,000行的表运行时，下面是我的结果:

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

如果您可以使用TABLESAMPLE，它将为您提供最佳性能。否则使用newwid ()/filter方法。如果结果集很大，Newid ()/order by应该是最后的选择。

用于MySQL获取随机记录

 SELECT name
  FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

详情http://jan.kneschke.de/projects/mysql/order-by-rand/