如何用单条SQL为5个唯一name_id各返回一个随机URL？

这个场景我之前做项目时也遇到过！用WHERE IN + RAND() + LIMIT的问题在于，它是先把所有符合name_id IN (...)的记录随机排序，再取前N条，完全不考虑每个name_id的分配，自然会出现有的name_id占多条、有的甚至没数据的情况。下面给你几种不同数据库环境下的靠谱解法：

适用于支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/ SQL Server等） #

用窗口函数ROW_NUMBER()按name_id分组，每组内随机排序后取第一条，这是最直观也最通用的方案：

SELECT name_id, url
FROM (
    SELECT 
        name_id, 
        url,
        ROW_NUMBER() OVER(PARTITION BY name_id ORDER BY RAND()) AS rn
    FROM link
    WHERE name_id IN (1,2,3,4,5) -- 替换成你的5个目标name_id
) AS temp
WHERE rn = 1;

逻辑说明： #

• 内层子查询先给每个name_id分组的记录，按随机顺序分配行号rn
• 外层只取每组行号为1的记录，这样每个指定的name_id就只会返回一条随机的URL

如果是PostgreSQL，把RAND()换成RANDOM()就行，其他逻辑完全一致：

SELECT name_id, url
FROM (
    SELECT 
        name_id, 
        url,
        ROW_NUMBER() OVER(PARTITION BY name_id ORDER BY RANDOM()) AS rn
    FROM link
    WHERE name_id IN (1,2,3,4,5)
) AS temp
WHERE rn = 1;

适用于MySQL 5.x（不支持窗口函数） #

如果你的MySQL版本比较旧，没法用窗口函数，可以用用户变量来模拟分组计数：

SELECT name_id, url
FROM (
    SELECT 
        name_id, 
        url,
        @row_num := IF(@prev_name = name_id, @row_num + 1, 1) AS rn,
        @prev_name := name_id
    FROM link, (SELECT @prev_name := NULL, @row_num := 0) AS vars
    WHERE name_id IN (1,2,3,4,5)
    ORDER BY name_id, RAND()
) AS temp
WHERE rn = 1;

逻辑说明： #

• 先按name_id排序，每组内再随机打乱顺序
• 用变量@prev_name记录上一条的name_id，@row_num记录当前组的行号，遇到新的name_id就重置行号为1
• 最后取每组行号为1的记录，同样实现每个name_id对应一条随机URL

这两种方案都能保证你指定的5个name_id各返回一条随机URL，不会出现分配不均的问题~

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