如何在MySQL中为每个分组获取n条随机记录

原数据表示例 #

目标结果示例 #

当前单用户查询代码 #

select data
from table 
where user = 'user1'
order by rand()
limit 2;

解决方案 #

根据不同数据库版本，提供两种可行的单条SQL方案：

方案一：窗口函数（推荐，适用于MySQL 8.0+/PostgreSQL/SQL Server/Oracle）

利用窗口函数实现分组内随机排序并筛选指定数量的记录：

SELECT User, Data
FROM (
    SELECT 
        User, 
        Data,
        -- 按用户分组，组内随机排序后生成序号
        ROW_NUMBER() OVER (PARTITION BY User ORDER BY RAND()) AS row_num
    FROM your_table_name -- 替换为你的实际表名
) AS ranked_data
WHERE row_num <= 2; -- 此处的2为每个用户要提取的记录数量，可按需修改

• 核心逻辑：
  1. PARTITION BY User 将数据按用户分组
  2. ORDER BY RAND() 让每个分组内的记录随机排序
  3. ROW_NUMBER() 给每个分组内的记录按随机顺序分配序号
  4. 外层筛选序号≤指定值的记录，得到每个用户的随机N条数据

方案二：兼容低版本MySQL（无窗口函数时使用）

如果你的MySQL版本低于8.0，不支持窗口函数，可以用以下替代方案（性能略逊于窗口函数）：

SELECT t1.User, t1.Data
FROM your_table_name t1
JOIN (
    SELECT User, FLOOR(RAND() * (COUNT(*) + 1)) AS rand_offset
    FROM your_table_name
    GROUP BY User
) t2 ON t1.User = t2.User
WHERE (
    SELECT COUNT(*) 
    FROM your_table_name t3 
    WHERE t3.User = t1.User AND t3.Data <= t1.Data
) <= 2; -- 指定提取数量

Oracle适配调整

Oracle中需将RAND()替换为DBMS_RANDOM.VALUE()，其余逻辑一致：

SELECT User, Data
FROM (
    SELECT 
        User, 
        Data,
        ROW_NUMBER() OVER (PARTITION BY User ORDER BY DBMS_RANDOM.VALUE()) AS row_num
    FROM your_table_name
)
WHERE row_num <= 2;

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