MySQL中为每个类别随机选取一行数据的实现方案

问题背景 #

现有如下结构的数据表：

需要实现每个category随机选取一个product，期望结果示例：

之前尝试的SQL语句无法满足需求：

SELECT
category, product
FROM
your_table
WHERE
category IN (1,2,3)
ORDER BY RAND()
LIMIT N

该语句仅能返回全局随机的N条数据，无法保证每个类别恰好返回一条，会出现类别重复或缺失的情况。

解决方案 #

根据不同数据库类型，提供以下可行的SQL写法：

MySQL（8.0及以上版本，支持窗口函数） #

利用ROW_NUMBER()窗口函数按类别分组，每组内随机排序后取第一条：

SELECT category, product
FROM (
    SELECT 
        category,
        product,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY RAND()) AS rn
    FROM your_table
    WHERE category IN (1,2,3)
) t
WHERE rn = 1;

MySQL（8.0以下版本） #

通过子查询生成随机值，分组后取每组随机排序的第一条数据：

SELECT t.category, t.product
FROM (
    SELECT 
        category, 
        product,
        RAND() AS rand_val
    FROM your_table
    WHERE category IN (1,2,3)
    ORDER BY rand_val
) t
GROUP BY t.category;

PostgreSQL #

使用RANDOM()函数配合窗口函数实现：

SELECT category, product
FROM (
    SELECT 
        category,
        product,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY RANDOM()) AS rn
    FROM your_table
    WHERE category IN (1,2,3)
) t
WHERE rn = 1;

SQL Server #

用NEWID()生成随机排序依据，结合窗口函数完成需求：

SELECT category, product
FROM (
    SELECT 
        category,
        product,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY NEWID()) AS rn
    FROM your_table
    WHERE category IN (1,2,3)
) t
WHERE rn = 1;

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