MySQL的子查询/加入

Question

好，我有一个查询这90％在那里，它只是造就了“未知列a.UserID错误在我读过周围试图排序问题where子句），并发现， SQL进程里面它不会知道我已经设置表A是因为这个问题，但它超出我的知识和专业知识，让它工作。我已经尝试了替代路线JOINs，而是总是回来相同。问题

查询是：

SELECT 
    a.*, 
    (SELECT AVG(c.PlayerTime) 
    FROM (
    SELECT PlayerTime 
    FROM elite_highscores b 
    WHERE a.UserID = b.UserID 
    ORDER BY PlayerTime ASC LIMIT 10 
    ) c 
) AS avgtime, 
    MIN(PlayerTime) as besttime, 
    MAX(TimeAdded) as time, 
    COUNT(UserID) 
FROM elite_highscores a 
WHERE Publish = 'Yes' 
GROUP BY UserID HAVING COUNT(UserID) >= 10 
ORDER BY avgtime ASC 

所以基本上我想要做的是选择从高分表中的一些数据（用户谁拥有超过10的高分记录），也计算平均时间（平均时间），但只根据该玩家的最后10个分数，这是什么导致我的问题，一切似乎工作正常。

表结构：

ScoreID int(16) 
UserID int(10) 
PlayerScore int(16) 
Publish enum('No', 'Hidden', 'Expired', 'Yes') 
TimeAdded int(16) 
PlayerIP varchar(18) 
Country varchar(2) 
PlayerTime int(10) 
code varchar(50) 

Answer 1

SELECT 
    a.UserID, 
    last10.avgtime, 
    MIN(a.PlayerTime) AS besttime, 
    MAX(a.TimeAdded) AS time, 
    COUNT(*) 
FROM 
     elite_highscores a 
    JOIN 
     (SELECT b.UserID, 
       AVG(b.PlayerTime) AS avgtime 
     FROM 
       (SELECT DISTINCT UserID 
       FROM elite_highscores 
      ) da 
      JOIN 
       elite_highscores b 
      ON b.UserID = da.UserID 
      AND b.PlayerTime <= 
       (SELECT c.PlayerTime 
        FROM elite_highscores c 
        WHERE c.UserID = da.UserID 
        ORDER BY c.PlayerTime ASC 
        LIMIT 1 OFFSET 9 
       ) 
     GROUP BY b.UserID 
    ) last10 
    ON last10.UserID = a.UserID 
WHERE a.Publish = 'Yes' 
GROUP BY a.UserID 
ORDER BY avgtime ASC