MYSQL - 三个表的总和点

Question

我有以下 SQL 语句(到目前为止):

SELECT p.facebookID,p.points,pi.facebookID,pi.points, SUM(pi.points) FROM player AS p 
INNER JOIN invites AS i ON p.id = i.inviteFromID 
INNER JOIN player AS pi ON i.inviteToId  = pi.facebookID
WHERE p.id = 1 GROUP BY pi.points    

这将显示 UserId = 1 邀请的用户以及他们获得了哪些积分。

目标:
我需要所选 UserId 的所有邀请点的总和。
我怎样才能做到这一点？我的方法正确吗？

Answer 1

你不应该按 pi.points 分组，而是按用户 ID 分组，我相信......试试这个

SELECT p.facebookID,p.points,pi.facebookID,pi.points, SUM(pi.points) FROM player AS p 
INNER JOIN invites AS i ON p.id = i.inviteFromID 
INNER JOIN player AS pi ON i.inviteToId  = pi.facebookID
WHERE p.id = 1 GROUP BY p.id

假设 p.id 是您的用户 ID

让我知道这是如何工作的