如何基于查询结果返回数据表中指定数量的随机记录

问题描述 #

我有一个名为Values的表，其中包含数千条记录（示例数据量要少得多）。除了部分记录共享同一个ID外，其余数据本身都是唯一的。
我需要编写一个查询，根据TestQ1的总计数返回对应数量的随机记录。举例来说，ID为120的查询总共有9条记录，那么每次运行查询时应该返回3条随机记录，这是Test表中指定的要求（该表的“Test”数值每周都会更新）。

图示说明 #

“Values”表是原始数据；“TestQ1”查询统计了每个特定“ID”对应的总记录数，其右侧是需要返回的记录数量。

现有实现 #

目前我只能实现到这一步：

SELECT TOP 5 Values.ID, Values.Test, Values.State, Rnd([Values]![ID]) AS [Random No], * FROM [Values] ORDER BY Rnd([Values]![ID]);

解决方案 #

你当前的写法只能固定返回全表前5条随机记录，无法按ID分组匹配每个ID需要返回的条数，适配Access环境的实现逻辑如下：
通过关联子查询对每个ID下的记录做随机排序编号，再筛选编号小于等于Test表中指定的返回条数即可，参考代码：

SELECT v.ID, v.Test, v.State
FROM [Values] AS v
INNER JOIN Test t ON v.ID = t.ID
WHERE (
    SELECT COUNT(*) 
    FROM [Values] v_sub
    WHERE v_sub.ID = v.ID 
    AND Rnd(-Timer() * v_sub.ID) <= Rnd(-Timer() * v.ID)
) <= t.RequiredReturnNum
ORDER BY v.ID, Rnd(-Timer() * v.ID);

注意事项 #

• 代码中t.RequiredReturnNum对应Test表中存储每个ID需要返回记录数的字段，可根据你的实际表结构修改字段名
• 使用Rnd(-Timer() * [ID])替换原来的Rnd([ID])，避免每次查询生成的随机顺序固定，实现每次运行都返回不同的随机记录
• 如果你使用的是MySQL、SQL Server等其他数据库，只需要替换随机函数即可：
  • MySQL替换Rnd(...)为RAND()
  • SQL Server替换Rnd(...)为NEWID()，使用窗口函数ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NEWID())的写法性能更优

内容的提问来源于stack exchange，提问作者MSAxes