求基于CTE的MySQL脚本：实现12名球员3天无重复同组的4人随机分组

核心思路 #

要满足「任意球员不会与同一队友重复同组」的要求，关键是记录每一轮的同组配对关系，后续分组时排除这些已存在的配对。具体步骤：

1. 生成基础球员列表
2. 随机生成首日分组，同步记录所有首日同组的两两配对
3. 次日分组时，确保组内任意两人均未在首日同组过
4. 第三日分组同理，排除前两日的所有配对记录

完整MySQL脚本 #

WITH Base_cte AS (
    -- 替换为你的球员ID查询，这里用1-12示例
    SELECT PlayerId FROM (
        SELECT 1 AS PlayerId UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
        UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ) AS Players
),
-- 第一天分组：随机分成3组，每组4人
Day1Groups AS (
    SELECT 
        CONCAT('Day1 Grp', CEIL((ROW_NUMBER() OVER(ORDER BY RAND()))/4)) AS GroupLabel,
        PlayerId
    FROM Base_cte
),
-- 记录第一天所有同组的两两配对（仅存A<B的组合，避免重复判断）
Day1Pairs AS (
    SELECT 
        LEAST(g1.PlayerId, g2.PlayerId) AS PlayerA,
        GREATEST(g1.PlayerId, g2.PlayerId) AS PlayerB
    FROM Day1Groups g1
    JOIN Day1Groups g2 
        ON g1.GroupLabel = g2.GroupLabel 
        AND g1.PlayerId != g2.PlayerId
),
-- 第二天分组：确保组内任意两人未在首日同组
Day2Groups AS (
    SELECT 
        CONCAT('Day2 Grp', CEIL((ROW_NUMBER() OVER(ORDER BY RAND()))/4)) AS GroupLabel,
        PlayerId
    FROM Base_cte bc
    -- 排除已有配对，确保选出来的球员之间无历史同组
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Base_cte bc2
        JOIN Day1Pairs dp ON (bc.PlayerId = dp.PlayerA AND bc2.PlayerId = dp.PlayerB)
        WHERE bc2.PlayerId < bc.PlayerId
    )
    -- 若出现分组冲突（概率极低），重新执行脚本即可
),
-- 记录第二天的同组配对
Day2Pairs AS (
    SELECT 
        LEAST(g1.PlayerId, g2.PlayerId) AS PlayerA,
        GREATEST(g1.PlayerId, g2.PlayerId) AS PlayerB
    FROM Day2Groups g1
    JOIN Day2Groups g2 
        ON g1.GroupLabel = g2.GroupLabel 
        AND g1.PlayerId != g2.PlayerId
),
-- 第三天分组：排除前两日所有配对
Day3Groups AS (
    SELECT 
        CONCAT('Day3 Grp', CEIL((ROW_NUMBER() OVER(ORDER BY RAND()))/4)) AS GroupLabel,
        PlayerId
    FROM Base_cte bc
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Base_cte bc2
        JOIN (SELECT * FROM Day1Pairs UNION SELECT * FROM Day2Pairs) allPairs 
            ON (bc.PlayerId = allPairs.PlayerA AND bc2.PlayerId = allPairs.PlayerB)
        WHERE bc2.PlayerId < bc.PlayerId
    )
)
-- 合并所有三天的分组结果
SELECT GroupLabel, PlayerId FROM Day1Groups
UNION ALL
SELECT GroupLabel, PlayerId FROM Day2Groups
UNION ALL
SELECT GroupLabel, PlayerId FROM Day3Groups
ORDER BY GroupLabel, PlayerId;

关键细节说明 #

• 配对记录优化：用LEAST()和GREATEST()只存储A<B的配对，减少重复数据，提升判断效率
• 分组逻辑：通过ROW_NUMBER() OVER(ORDER BY RAND())随机排序后，用CEIL(ROW_NUMBER()/4)自动分配组号
• 冲突处理：由于是随机分组，极少数情况下可能出现无法生成符合要求的分组，此时重新执行脚本即可
• 可扩展性：若要调整球员数量或分组规模，只需修改Base_cte和分组的除数（每组人数）

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