多表Left Join异常：无法获取预期结果的排查与解决

问题背景 #

需查询特定玩家的旧游戏记录及对局双方的评分变化，评分数据存储在ratingverloop表中。当前使用两次LEFT JOIN关联该表时，出现白方（whitePlayer）和黑方（blackPlayer）的评分数据匹配错误，导致userID1与userID2重复的异常。

错误SQL语句 #

$sql = "SELECT games.gameID
                ,whitePlayer
                ,blackPlayer
                ,gameMessage
                ,messageFrom
                ,date_format(dateCreated , '%d-%m-%Y') AS startdatum
                ,lastMove
                ,date_format(lastMove , '%d-%m-%Y') AS lastMovex
                ,whiteNick
                ,blackNick
                ,rv1.userID AS userID1
                ,rv1.ratingvan AS userID1_ratingvan
                ,rv1.rating AS userID1_rating
                
                ,rv2.userID AS userID2
                ,rv2.ratingvan AS userID2_ratingvan
                ,rv2.rating AS userID2_rating
            
        FROM games 
        LEFT JOIN ratingverloop rv1 ON games.gameID = rv1.gameID
        LEFT JOIN ratingverloop rv2 ON games.gameID = rv2.gameID
        WHERE
            (
                gameMessage <> '' 
                AND gameMessage <> 'playerInvited'
                AND gameMessage <> 'inviteDeclined'
            )
            AND 
            (
                whitePlayer = :playerID
                OR blackPlayer = :playerID
            ) 
        ORDER BY lastMove DESC";

问题根源 #

ratingverloop表中同一gameID对应多条记录（对局双方各一条，游戏修正时可能产生更多），原SQL仅通过gameID关联，未绑定玩家ID，导致关联时出现笛卡尔积，无法精准匹配白方和黑方的对应评分数据。

修复后的SQL方案 #

在两次LEFT JOIN时，增加玩家ID的匹配条件，分别关联白方和黑方的评分记录：

$sql = "SELECT games.gameID
                ,whitePlayer
                ,blackPlayer
                ,gameMessage
                ,messageFrom
                ,date_format(dateCreated , '%d-%m-%Y') AS startdatum
                ,lastMove
                ,date_format(lastMove , '%d-%m-%Y') AS lastMovex
                ,whiteNick
                ,blackNick
                ,rv1.userID AS userID1
                ,rv1.ratingvan AS userID1_ratingvan
                ,rv1.rating AS userID1_rating
                
                ,rv2.userID AS userID2
                ,rv2.ratingvan AS userID2_ratingvan
                ,rv2.rating AS userID2_rating
            
        FROM games 
        -- 关联白方的评分记录
        LEFT JOIN ratingverloop rv1 ON games.gameID = rv1.gameID AND games.whitePlayer = rv1.userID
        -- 关联黑方的评分记录
        LEFT JOIN ratingverloop rv2 ON games.gameID = rv2.gameID AND games.blackPlayer = rv2.userID
        WHERE
            (
                gameMessage <> '' 
                AND gameMessage <> 'playerInvited'
                AND gameMessage <> 'inviteDeclined'
            )
            AND 
            (
                whitePlayer = :playerID
                OR blackPlayer = :playerID
            ) 
        ORDER BY lastMove DESC";

进阶优化（处理多记录场景） #

如果ratingverloop表中同一gameID+userID存在多条记录（比如游戏修正的评分调整），可通过聚合函数取最新的评分记录：

$sql = "SELECT games.gameID
                ,whitePlayer
                ,blackPlayer
                ,gameMessage
                ,messageFrom
                ,date_format(dateCreated , '%d-%m-%Y') AS startdatum
                ,lastMove
                ,date_format(lastMove , '%d-%m-%Y') AS lastMovex
                ,whiteNick
                ,blackNick
                ,rv1.userID AS userID1
                ,rv1.ratingvan AS userID1_ratingvan
                ,rv1.rating AS userID1_rating
                
                ,rv2.userID AS userID2
                ,rv2.ratingvan AS userID2_ratingvan
                ,rv2.rating AS userID2_rating
            
        FROM games 
        LEFT JOIN (
            SELECT userID, gameID, ratingvan, rating
            FROM ratingverloop
            WHERE (gameID, userID, datumtijd) IN (
                SELECT gameID, userID, MAX(datumtijd)
                FROM ratingverloop
                GROUP BY gameID, userID
            )
        ) rv1 ON games.gameID = rv1.gameID AND games.whitePlayer = rv1.userID
        LEFT JOIN (
            SELECT userID, gameID, ratingvan, rating
            FROM ratingverloop
            WHERE (gameID, userID, datumtijd) IN (
                SELECT gameID, userID, MAX(datumtijd)
                FROM ratingverloop
                GROUP BY gameID, userID
            )
        ) rv2 ON games.gameID = rv2.gameID AND games.blackPlayer = rv2.userID
        WHERE
            (
                gameMessage <> '' 
                AND gameMessage <> 'playerInvited'
                AND gameMessage <> 'inviteDeclined'
            )
            AND 
            (
                whitePlayer = :playerID
                OR blackPlayer = :playerID
            ) 
        ORDER BY lastMove DESC";

测试验证结果 #

修复后的查询将正确匹配双方评分数据：

(
    [gameID] => 2786547
    [whitePlayer] => 2187
    [blackPlayer] => 7144
    [gameMessage] => checkMate
    [messageFrom] => black
    [startdatum] => 23-01-2023
    [lastMove] => 2023-03-03 00:46:40
    [lastMovex] => 03-03-2023
    [whiteNick] => @Gijs
    [blackNick] => E-Street
    [userID1] => 2187
    [userID1_ratingvan] => 938
    [userID1_rating] => 933
    [userID2] => 7144
    [userID2_ratingvan] => 1193
    [userID2_rating] => 1198
)

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