mysql - on 子句中存在的未知列

Question

加入另一个表时出现问题。

SELECT rank FROM  
    (
        SELECT highscores.playerID, (@rowID := @rowID + 1) AS rank 
        FROM highscores, (SELECT @rowID := 0) r
        JOIN overall ON highscores.playerID = overall.playerID
        WHERE skillID = ?
        ORDER BY skillExperience DESC, highscores.updateTime ASC
    ) data  
WHERE data.playerID = ?

一直告诉我 Unknown column 'highscores.playerID' in 'on clause' 当它存在时。我在 Internet 上查找了此问题的解决方案，但没有找到适合我的解决方案。

导致问题的行号在这里吗？如果是这样，我该如何解决这个问题？

Answer 1

你必须首先加入高分表和总表:

SELECT rank FROM  
    (
        SELECT highscores.playerID, (@rowID := @rowID + 1) AS rank 
        FROM
          highscores JOIN overall ON highscores.playerID = overall.playerID,
          (SELECT @rowID := 0) r
        WHERE skillID = ?
        ORDER BY skillExperience DESC, highscores.updateTime ASC
    ) data  
WHERE data.playerID = ?

在您的原始查询中，您将派生表 r 与整个表连接起来:

(SELECT @rowID := 0) r
JOIN overall ON highscores.playerID = overall.playerID

这是行不通的，因为找不到 highscores.playerID，因为 highscores 表将单独连接。