合并3个SQL查询计算赛事积分异常问题求助

需求说明 #

查询指定用户（$POSTCharID）参与的所有赛事，按以下规则计算每场赛事主队和客队的总积分：

• 未参赛且未准备：1分/人
• 参赛未准备：5分/人
• 未参赛但准备：10分/人
• 参赛且准备：15分/人

现有问题 #

1. 积分计算偏差：例如$POSTCharID=20时，当前计算结果为HomeTotalPts=350、AwayTotalPts=192，预期应为25和47
2. 赛事记录缺失：例如$POSTCharID=12时，仅显示1场赛事，实际应显示2场，且积分结果不符合预期

相关表结构 #

• GameParti：记录用户参与赛事的信息
  -</think_never_used_51bce0c785ca2f68081bfa7d91973934></think_never_used_51bce0c785ca2f68081bfa7d91973934>
• LeagueGames：记录赛事基本信息（比分、主客队等）
• Teams：队伍信息
• Clubs：俱乐部信息
• TeamPlayerLnk：队员与队伍的关联信息
• Characters：用户角色信息
• PlayerReady：队员准备状态信息

原始错误SQL语句 #

SELECT    
    s.CreationDate          AS sCreationDate,
    s.Score_HomeTeam        AS hScore,
    hc.Clubname    AS hClubName,
    ht.Team_Name            AS hTeamName,
    s.Score_AwayTeam        AS aScore,
    ac.Clubname    AS aClubName,
    at.Team_Name            AS aTeamName,
    s.LeagueGame_Status     AS sGameStatus,
    sum(CASE    WHEN z1.LeagueGames_ID IS NULL AND p1.PlayerReady_ID IS NULL THEN '1' 
                WHEN z1.LeagueGames_ID IS NOT NULL AND p1.PlayerReady_ID IS NULL THEN '5' 
                WHEN z1.LeagueGames_ID IS NULL AND p1.PlayerReady_ID IS NOT NULL THEN '10' 
                WHEN z1.LeagueGames_ID IS NOT NULL AND p1.PlayerReady_ID IS NOT NULL THEN '15' ELSE 0 END) AS HomeTotalPts,
    sum(CASE    WHEN z2.LeagueGames_ID IS NULL AND p2.PlayerReady_ID IS NULL THEN '1' 
                WHEN z2.LeagueGames_ID IS NOT NULL AND p2.PlayerReady_ID IS NULL THEN '5' 
                WHEN z2.LeagueGames_ID IS NULL AND p2.PlayerReady_ID IS NOT NULL THEN '10' 
                WHEN z2.LeagueGames_ID IS NOT NULL AND p2.PlayerReady_ID IS NOT NULL THEN '15' ELSE 0 END) AS AwayTotalPts                       
FROM        GameParti e
LEFT JOIN   LeagueGames s           ON s.LeagueGames_ID = e.LeagueGames_ID
LEFT JOIN   Teams ht     ON ht.TeamID = s.Home_TeamID
LEFT JOIN   Clubs hc          ON hc.ClubID = ht.Club_ID 
LEFT JOIN   Teams at     ON at.TeamID = s.Away_TeamID
LEFT JOIN   Clubs ac          ON ac.ClubID = at.Club_ID 

LEFT JOIN   TeamPlayerLnk g1     ON g1.Teamplayer_TeamID = ht.TeamID AND g1.ActLinked = '1'
LEFT JOIN   Characters c1              ON c1.ID = g1.CharacterID 
LEFT JOIN   GameParti z1           ON z1.CharacterID = g1.CharacterID AND z1.LeagueGames_ID = s.LeagueGames_ID
LEFT JOIN   PlayerReady p1   ON p1.PlayerReady_ID = g1.CharacterID AND p1.ActLinked = '1' AND p1.CreatedByCharacterID='$POSTCharID'

LEFT JOIN   TeamPlayerLnk g2     ON g2.Teamplayer_TeamID = at.TeamID AND g2.ActLinked = '1'
LEFT JOIN   Characters c2              ON c2.ID = g2.CharacterID 
LEFT JOIN   GameParti z2           ON z2.CharacterID = g2.CharacterID AND z2.LeagueGames_ID = s.LeagueGames_ID
LEFT JOIN   PlayerReady p2   ON p2.PlayerReady_ID = g2.CharacterID AND p2.ActLinked = '1' AND p2.CreatedByCharacterID='$POSTCharID'
WHERE e.CharacterID = '$POSTCharID' ORDER BY CreationDate;

问题根源分析 #

1. 笛卡尔积导致数据重复：直接关联主队/客队所有队员数据，单场赛事记录被队员数重复放大，SUM计算的是重复后的数值，积分严重偏高
2. 缺少分组逻辑：未按赛事维度分组，SUM会累加所有赛事积分，同时连接操作过滤掉部分赛事记录
3. 字符串数值错误：CASE语句中使用'1'这类字符串，SUM时可能触发非预期的类型转换
4. 准备状态关联逻辑错误：p1.PlayerReady_ID = g1.CharacterID关联逻辑不合理，应按队员ID关联准备状态

修正后的SQL语句 #

SELECT    
    s.CreationDate          AS sCreationDate,
    s.Score_HomeTeam        AS hScore,
    hc.Clubname             AS hClubName,
    ht.Team_Name            AS hTeamName,
    s.Score_AwayTeam        AS aScore,
    ac.Clubname             AS aClubName,
    at.Team_Name            AS aTeamName,
    s.LeagueGame_Status     AS sGameStatus,
    -- 计算主队总积分：子查询单独统计每队队员积分总和
    (SELECT SUM(
        CASE
            WHEN EXISTS(SELECT 1 FROM GameParti z WHERE z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID) 
                 AND EXISTS(SELECT 1 FROM PlayerReady p WHERE p.CharacterID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = '$POSTCharID') THEN 15
            WHEN EXISTS(SELECT 1 FROM GameParti z WHERE z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID) 
                 AND NOT EXISTS(SELECT 1 FROM PlayerReady p WHERE p.CharacterID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = '$POSTCharID') THEN 5
            WHEN NOT EXISTS(SELECT 1 FROM GameParti z WHERE z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID) 
                 AND EXISTS(SELECT 1 FROM PlayerReady p WHERE p.CharacterID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = '$POSTCharID') THEN 10
            ELSE 1
        END
    ) FROM TeamPlayerLnk g WHERE g.Teamplayer_TeamID = ht.TeamID AND g.ActLinked = '1') AS HomeTotalPts,
    -- 计算客队总积分：逻辑同上
    (SELECT SUM(
        CASE
            WHEN EXISTS(SELECT 1 FROM GameParti z WHERE z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID) 
                 AND EXISTS(SELECT 1 FROM PlayerReady p WHERE p.CharacterID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = '$POSTCharID') THEN 15
            WHEN EXISTS(SELECT 1 FROM GameParti z WHERE z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID) 
                 AND NOT EXISTS(SELECT 1 FROM PlayerReady p WHERE p.CharacterID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = '$POSTCharID') THEN 5
            WHEN NOT EXISTS(SELECT 1 FROM GameParti z WHERE z.CharacterID = g.CharacterID AND z.LeagueGames_ID = s.LeagueGames_ID) 
                 AND EXISTS(SELECT 1 FROM PlayerReady p WHERE p.CharacterID = g.CharacterID AND p.ActLinked = '1' AND p.CreatedByCharacterID = '$POSTCharID') THEN 10
            ELSE 1
        END
    ) FROM TeamPlayerLnk g WHERE g.Teamplayer_TeamID = at.TeamID AND g.ActLinked = '1') AS AwayTotalPts                       
FROM        GameParti e
LEFT JOIN   LeagueGames s ON s.LeagueGames_ID = e.LeagueGames_ID
LEFT JOIN   Teams ht ON ht.TeamID = s.Home_TeamID
LEFT JOIN   Clubs hc ON hc.ClubID = ht.Club_ID 
LEFT JOIN   Teams at ON at.TeamID = s.Away_TeamID
LEFT JOIN   Clubs ac ON ac.ClubID = at.Club_ID 
WHERE e.CharacterID = '$POSTCharID'
GROUP BY s.CreationDate, s.Score_HomeTeam, hc.Clubname, ht.Team_Name, s.Score_AwayTeam, ac.Clubname, at.Team_Name, s.LeagueGame_Status
ORDER BY s.CreationDate;

修正说明 #

1. 子查询计算积分：针对主客队分别用关联子查询统计队员积分总和，避免多表连接产生笛卡尔积
2. 添加GROUP BY分组：按赛事核心字段分组，确保每场赛事仅返回一条记录，解决赛事缺失问题
3. 修正数值类型：CASE语句直接使用数值1/5/10/15，避免字符串转换错误
4. 优化状态判断逻辑：用EXISTS子查询判断队员参赛/准备状态，逻辑更清晰且避免数据重复
5. 调整准备状态关联：假设PlayerReady通过CharacterID关联队员，若表结构不同可自行调整对应字段

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