php - 有什么方法可以找到查询的值吗？

Question

我最近制作了一个系统，根据每个玩家的分数对他们进行排名。嗯，系统获取积分的方式相当困惑。使用这个系统超过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

既然已经显示了，我想知道是否有任何方法可以获取“点”的值并以某种方式显示它，以便我可以验证该数字。这可能吗？

Answer 1

查询中没有 group by 语句，因此 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;