mysql - SQL为每个PlayerID获取行的总和

我的布局看起来像这样:

Player:
PlayerID - PlayerName

Exp
ExpID - PlayerID - ExpChange

我想要做的是获取每个玩家名的ExpChange的总和，并按expchange的总和对其进行排序。 (以SUM(ExpChange)作为得分的高得分列表。

这是我到目前为止的内容:

SELECT Player.PlayerName, Exp.ExpChange
FROM Player
INNER JOIN Exp
ON Player.PlayerID=Exp.PlayerID
ORDER BY Exp.ExpChange;

但是现在，我需要以某种方式将每个玩家的总费用乘以该玩家的总支出进行排序。这可能吗？我试图搜索一吨左右，但找不到任何东西。谢谢

P.S.我也尝试过这样的事情:

SELECT Player.PlayerName, SUM(Exp.ExpChange) AS exp
FROM Player
INNER JOIN Exp
ON Player.PlayerID=Exp.PlayerID
ORDER BY exp;

但它只会输出所有人的总ExpChange，而不是每个玩家的总ExpChange。

最佳答案

您需要添加GROUP BY子句以获取每个sum()的player:

SELECT Player.PlayerName, SUM(COALESCE(Exp.ExpChange, 0)) AS exp
FROM Player
LEFT JOIN Exp
   ON Player.PlayerID=Exp.PlayerID
GROUP BY Player.PlayerName
ORDER BY exp;

如果只希望exp表中具有匹配项的记录，则可以使用INNER JOIN:

SELECT Player.PlayerName, SUM(Exp.ExpChange) AS exp
FROM Player
INNER JOIN Exp
   ON Player.PlayerID=Exp.PlayerID
GROUP BY Player.PlayerName
ORDER BY exp;

关于mysql - SQL为每个PlayerID获取行的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14164103/