我最近制作了一个系统,根据每个玩家的分数对他们进行排名。这个系统获得分数的方式相当混乱。在使用这个系统超过24小时后,我发现它没有按要点组织。但后来我突然想到,我可能以一种不代表SQL查询的方式计算错误的点。以下是我的排名使用的SQL查询:
SELECT * , playeruid AS player_id, (
(
SELECT COALESCE(sum(player1points),0)
FROM `mybb_matches`
WHERE player1uid = player_id AND gid = $id AND timestamp < $time AND winneruid is NOT NULL AND dispute != 3 )
+
(
SELECT COALESCE(sum(player2points),0)
FROM `mybb_matches`
WHERE player2uid = player_id AND gid = $id AND timestamp < $time AND winneruid is NOT NULL AND dispute != 3 )
+
(
SELECT SUM( rank )
FROM `mybb_matchesgame`
WHERE playeruid = player_id AND gid = $id )
)
AS points
FROM mybb_matchesgame WHERE gid = $id
ORDER BY points DESC
现在显示了这一点,我想知道是否有任何方法可以获取"点"的值并以某种方式显示它,以便验证数字。这可能吗?
查询中没有groupby语句,因此SUM很可能没有超过预期的集合。此外,COALESCE可以替换为IFNULL,这可能会更有效率。
SELECT q.* , playeruid AS player_id, a.points+b.points+c.points AS points
FROM mybb_matchesgame q
LEFT JOIN (
SELECT IFNULL(SUM(player1points),0) as points,player_id
FROM `mybb_matches`
WHERE timestamp < $time AND winneruid is NOT NULL AND dispute != 3
GROUP BY player_id) a ON player1uid = a.player_id
LEFT JOIN (
SELECT IFNULL(sum(player2points),0) as points,player_id
FROM `mybb_matches`
WHERE timestamp < $time AND winneruid is NOT NULL AND dispute != 3
GROUP BY player_id) b ON player2uid = b.player_id
LEFT JOIN (
SELECT IFNULL(SUM( rank ),0) as points,player_id
FROM `mybb_matchesgame`
GROUP BY player_id) c ON playeruid = c.player_id
WHERE gid = $id
ORDER BY a.points+b.points+c.points DESC;