Joins and SUMS?

Question

嗨，有下表：团队， 团队积分， team_members， 积分表我正在尝试对团队成员积分求和，然后将团队积分求和：

Teams
|TeamID |TeamName  | TeamLocation|
 -------------------------------
|1      | sm1        | location1|
|2      | sm2        | location2|

TeamPoints
|TeamID |TotalPonts  | RemainderPonts|
 -------------------------------
|1      | 10         | 7            |
|1      | 8          | 6            |
|2      | 8          | 6            |

team_members
|TeamID |UserID  | 
 -----------------
|1      | 1      | 
|1      | 2      |
|2      | 3      |  

pointsTable
|UserID |TotalPonts  | RemainderPonts|
 -------------------------------
|1      | 10         | 7            |
|2      | 8          | 6            |
|2      | 8          | 6            |

so results for team sm1 (TeamID =1) should be
TeamName=sm1,TeamPoints.TotalPonts =18, TeamPoints.RemainderPonts=13
pointsTable.TotalPonts =26, pointsTable.RemainderPonts=19

这可以对成员积分求和，但团队积分有问题

SELECT 
    teams.TeamID AS theTeamID,
    teams.TeamName,
    teams.TeamLocation,
    team_members.TeamID,
    team_members.UserID,
    SUM(pointstable.RemainderPoints) AS points
FROM
    teams
        LEFT JOIN
    team_members ON teams.TeamID = team_members.TeamID
        LEFT JOIN
    pointstable ON team_members.UserID = pointstable.UserID
GROUP BY teams.TeamID

试过

SELECT 
    teams.TeamID AS theTeamID,
    teams.TeamName,
    teams.TeamLocation,
    team_members.TeamID,
    team_members.UserID,
    SUM(pointstable.RemainderPoints) AS points,
    teampoints.TeamID AS tpID,
    SUM(teampoints.TotalPoints) AS teamRedeamable
FROM
    teams
        LEFT JOIN
    team_members ON teams.TeamID = team_members.TeamID
        LEFT JOIN
    pointstable ON team_members.UserID = pointstable.UserID
        LEFT JOIN
    teampoints ON teams.TeamID = teampoints.TeamID
GROUP BY teams.TeamID

但似乎更改/乘以原始值也尝试过子选择，但需要一些如何分组？？

欢迎任何帮助

Answer 1

认为您需要一个子选择才能从用户积分表中获取详细信息，并将其与主表连接起来：-

SELECT 
    teams.TeamID AS theTeamID,
    teams.TeamName,
    teams.TeamLocation,
    SUM(pointstable.RemainderPoints) AS RemainderPonts,
    SUM(pointstable.TotalPonts ) AS TotalPonts,
    Sub1.UserTotalPoints, 
    Sub1.UserRemainderPoints,
    Sub2.TeamTotalPoints,
    Sub2.TeamRemainderPoints
FROM teams
LEFT JOIN team_members ON teams.TeamID = team_members.TeamID
LEFT JOIN pointstable ON team_members.UserID = pointstable.UserID
LEFT OUTER JOIN
(
    SELECT TeamID, SUM(TotalPoints) AS UserTotalPoints, SUM(RemainderPoints) AS UserRemainderPoints
    FROM team_members
    INNER JOIN pointsTable
    ON team_members.UserID = pointsTable.UserID
    GROUP BY TeamID
) Sub1
ON Sub1.TeamID = teams.TeamID
LEFT OUTER JOIN
(
    SELECT TeamID, SUM(TotalPoints) AS TeamTotalPoints, SUM(RemainderPoints) AS TeamRemainderPoints
    FROM TeamPoints
    GROUP BY TeamID
) Sub2
ON Sub0.theTeamID = Sub2.TeamID
GROUP BY teams.TeamID, teams.TeamName, teams.TeamLocation

请注意，这有点狡猾，因为它在 SELECT 中具有不在 GROUP BY 子句中的非聚合列。在这种情况下，使用默认的MySQL设置，这应该没问题，但是如果使用不同的数据库（或取决于MySQL设置），则可能必须将其作为2个不同的子选择并将结果连接在一起。

SELECT Sub0.theTeamID, Sub0.TeamName, Sub0.TeamLocation, Sub0.RemainderPonts, Sub0.TotalPonts, Sub1.UserTotalPoints, Sub1.UserRemainderPoints, Sub2.TeamTotalPoints, Sub2.TeamRemainderPoints
FROM 
(
    SELECT 
        teams.TeamID AS theTeamID,
        teams.TeamName,
        teams.TeamLocation,
        SUM(pointstable.RemainderPoints) AS RemainderPonts,
        SUM(pointstable.TotalPonts ) AS TotalPonts
    FROM teams
    LEFT OUTER JOIN team_members ON teams.TeamID = team_members.TeamID
    LEFT OUTER JOIN pointstable ON team_members.UserID = pointstable.UserID
) Sub0
LEFT OUTER JOIN
(
    SELECT TeamID, SUM(TotalPoints) AS UserTotalPoints, SUM(RemainderPoints) AS UserRemainderPoints
    FROM team_members
    INNER JOIN pointsTable
    ON team_members.UserID = pointsTable.UserID
    GROUP BY TeamID
) Sub1
ON Sub0.theTeamID = Sub1.TeamID
LEFT OUTER JOIN
(
    SELECT TeamID, SUM(TotalPoints) AS TeamTotalPoints, SUM(RemainderPoints) AS TeamRemainderPoints
    FROM TeamPoints
    GROUP BY TeamID
) Sub2
ON Sub0.theTeamID = Sub2.TeamID