MySQL中如何为好友表每行关联水果表的3条随机记录

要实现每个好友独立分配3个随机水果，核心是让每个好友的水果选择过程独立，而非共享同一组随机结果。以下分两种场景给出实现方式：

适用于MySQL 8.0+（支持窗口函数） #

使用ROW_NUMBER()窗口函数，按好友分组后对水果随机排序，取每组前3条：

INSERT INTO FriendsAndFruits(FirstName, FruitName)
SELECT FirstName, FruitName
FROM (
    SELECT 
        f.FirstName, 
        fr.FruitName,
        -- 按好友分组，对水果随机排序并生成行号
        ROW_NUMBER() OVER (PARTITION BY f.FirstName ORDER BY RAND()) AS rn
    FROM TempFriends f
    -- 生成每个好友与所有水果的笛卡尔积
    CROSS JOIN TempFruits fr
) t
-- 筛选每个好友的前3个随机水果
WHERE rn <= 3;

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

使用用户变量模拟分组排序，实现相同效果：

INSERT INTO FriendsAndFruits(FirstName, FruitName)
SELECT FirstName, FruitName
FROM (
    SELECT 
        f.FirstName, 
        fr.FruitName,
        -- 切换好友时重置行号，否则递增
        @rn := IF(@prev = f.FirstName, @rn + 1, 1) AS rn,
        @prev := f.FirstName
    FROM TempFriends f
    CROSS JOIN TempFruits fr
    -- 初始化变量
    CROSS JOIN (SELECT @prev := '', @rn := 0) vars
    -- 按好友分组，水果随机排序
    ORDER BY f.FirstName, RAND()
) t
WHERE rn <= 3;

逻辑说明 #

1. 先通过CROSS JOIN生成每个好友与所有水果的组合；
2. 对每个好友的水果集合单独随机排序，避免所有好友共享同一组水果；
3. 筛选出每个好友的前3个随机水果，插入到交集表中。

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