自动获取TeamID的两队随机球员匹配SQL问题排查

现有数据库包含两支球队，通过TeamPlayerMapping表存储球员与球队的关联（PlayerID与TeamID）。需要实现从两队随机选取球员插入Match表，要求自动获取TeamID而非手动指定，但当前SQL代码仅Team1正常工作，Team2无法生成有效配对。

现有SQL代码 #

WITH RandomPlayers AS (
    SELECT
        tp.PlayerId,
        tp.TeamId,
        ROW_NUMBER() OVER (PARTITION BY tp.TeamId ORDER BY NEWID()) AS RowNum
    FROM
        TeamPlayerMapping tp
    WHERE
        tp.TeamId IN (15, 19)
)
INSERT INTO Match (Player_1_id, Player_2_id)
SELECT
    Team1.PlayerId AS Player1Id,
    Team2.PlayerId AS Player2Id
FROM
    (SELECT PlayerId, RowNum FROM RandomPlayers WHERE TeamId = 15 ) AS Team1
CROSS APPLY (
    SELECT TOP 1 PlayerId
    FROM RandomPlayers Team2
    WHERE Team2.TeamId = 19 AND Team2.RowNum = Team1.RowNum
    ORDER BY NEWID()
) AS Team2
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Match m
        WHERE (m.Player_2_id = Team2.PlayerId)
           OR (m.Player_1_id  = Team1.PlayerId)
);

相关表结构 #

TeamPlayerMapping表

MapId  TeamId PlayerID
254       15    31
255       19    34
256       15    35
257       19    30
258       15    29
259       19    33
260       15    32
261       19    36                       

Match表（现有数据）

MatchID Player_1_id  Player_2_id
258             35           36
259             29           34
260             31           36 
261             32           36

原代码问题分析 #

1. Team2配对逻辑缺陷：通过Team2.RowNum = Team1.RowNum关联，仅当两队球员数量完全一致且RowNum匹配时才会返回结果，一旦某队球员因已存在于Match表被排除，对应RowNum的Team2球员也无法被选中，导致Team2无法生成有效配对。
2. 硬编码TeamID：代码中手动指定了15和19，无法自动适配球队ID。
3. 重复判断不严谨：仅排除已有Player_1或Player_2的记录，但未避免重复的球员配对组合（如(35,36)和(36,35)）。

修正后的SQL代码 #

WITH Teams AS (
    -- 自动获取参与比赛的两支球队ID（若需指定球队，可添加WHERE TeamId IN (xxx, xxx)）
    SELECT DISTINCT TeamId FROM TeamPlayerMapping
    ORDER BY NEWID()
    OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY
),
RandomTeamPlayers AS (
    -- 为每支球队的球员生成随机排序
    SELECT
        tp.PlayerId,
        tp.TeamId,
        ROW_NUMBER() OVER (PARTITION BY tp.TeamId ORDER BY NEWID()) AS RowNum
    FROM TeamPlayerMapping tp
    JOIN Teams t ON tp.TeamId = t.TeamId
),
TeamPairs AS (
    -- 拆分两支球队为TeamA和TeamB
    SELECT
        MAX(CASE WHEN rn = 1 THEN TeamId END) AS TeamAId,
        MAX(CASE WHEN rn = 2 THEN TeamId END) AS TeamBId
    FROM (
        SELECT TeamId, ROW_NUMBER() OVER (ORDER BY TeamId) AS rn
        FROM Teams
    ) t
)
INSERT INTO Match (Player_1_id, Player_2_id)
SELECT
    a.PlayerId AS Player_1_id,
    b.PlayerId AS Player_2_id
FROM RandomTeamPlayers a
CROSS JOIN RandomTeamPlayers b
JOIN TeamPairs tp ON a.TeamId = tp.TeamAId AND b.TeamId = tp.TeamBId
-- 检查配对是否已存在（双向组合都需排除）
WHERE NOT EXISTS (
    SELECT 1 FROM Match m
    WHERE (m.Player_1_id = a.PlayerId AND m.Player_2_id = b.PlayerId)
       OR (m.Player_1_id = b.PlayerId AND m.Player_2_id = a.PlayerId)
)
-- 随机选取一组配对（调整TOP数值可生成多组）
ORDER BY NEWID()
TOP 1;

代码改进说明 #

1. 自动获取TeamID：通过Teams CTE自动提取数据库中的两支球队ID，无需硬编码；若需指定特定球队，可在Teams CTE中添加筛选条件。
2. 修复配对逻辑：使用CROSS JOIN结合随机排序，打破原代码中RowNum匹配的限制，确保两队球员能自由随机配对。
3. 严谨的重复判断：同时检查正向和反向的配对组合，避免生成重复的比赛记录。
4. 灵活生成配对：调整TOP数值可一次性生成多组随机配对，满足不同需求。

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