MySQL技术问题：如何用DISTINCT/GROUP BY随机展示唯一product id对应id

问题描述 #

• 使用GROUP BY时无法显示id字段；使用DISTINCT会返回所有id和product id的组合
• 需求：获取唯一的product id，并随机展示每个product id对应的一条id及对应price
• 尝试的错误SQL：select id,distinct(product id),price from price order by rand()

原始数据 #

期望结果 #

解决方案 #

1. MySQL/MariaDB #

方法一：窗口函数（推荐，MySQL 8.0+）

利用窗口函数按product id分组，随机排序后取每组第一条：

SELECT id, `product id`, price
FROM (
    SELECT 
        id, 
        `product id`, 
        price,
        ROW_NUMBER() OVER (PARTITION BY `product id` ORDER BY RAND()) AS rn
    FROM price
) t
WHERE rn = 1;

方法二：兼容低版本MySQL

通过GROUP_CONCAT拼接随机排序的字段，再截取第一个值：

SELECT 
    SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY RAND()), ',', 1) AS id,
    `product id`,
    SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY RAND()), ',', 1) AS price
FROM price
GROUP BY `product id`;

注意：如果id或price值长度较大，需调整group_concat_max_len参数避免内容截断。

2. PostgreSQL #

用ROW_NUMBER()窗口函数结合RANDOM()实现随机取数：

SELECT id, "product id", price
FROM (
    SELECT 
        id, 
        "product id", 
        price,
        ROW_NUMBER() OVER (PARTITION BY "product id" ORDER BY RANDOM()) AS rn
    FROM price
) t
WHERE rn = 1;

3. SQL Server #

通过NEWID()生成随机排序依据，再取每组第一条：

SELECT id, [product id], price
FROM (
    SELECT 
        id, 
        [product id], 
        price,
        ROW_NUMBER() OVER (PARTITION BY [product id] ORDER BY NEWID()) AS rn
    FROM price
) t
WHERE rn = 1;

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