我需要显示一个图表,其中可以显示游戏,得分,所有时间排名,每周排名,排名与朋友。
我有两张桌子:
1)高分(playerid,gameid,score,time(timestamp))
2)朋友(playerid,friendid)
我想知道如何使用最少的查询数来实现这一点。
我在游戏网站上使用PHP和MySQL。

最佳答案

可以如下查询:

SELECT
    gamescores.gameid,
    COUNT(*)+1 AS rank,
    playergamescores.total,
    gamename,
    foldername
FROM (
    SELECT gameid, playerid, SUM( score ) AS total, time
    FROM high_scores
    GROUP BY gameid, playerid
    ORDER BY gameid, total DESC
) AS gamescores
INNER JOIN
(
    SELECT gameid, SUM(score) AS total
    FROM high_scores
    WHERE playerid = 361822
    GROUP BY gameid
    ORDER BY total DESC
) AS playergamescores
ON playergamescores.gameid = gamescores.gameid
INNER JOIN gamemaster
ON gamescores.gameid = gamemaster.gameid
WHERE gamescores.total > (
    SELECT SUM( score ) AS total
    FROM high_scores
    WHERE gamescores.gameid = gameid
    AND playerid = 361822
)
GROUP BY gamescores.gameid
ORDER BY gamescores.time DESC

无论如何谢谢你的回答。。。
-导航

关于sql - 根据玩过的游戏计算玩家排名,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/4712093/

10-12 06:26