具有匹配和求和的 MySQL 查询 - 联赛表

Question

我有 5 个表:

• 玩家(id、姓名、team_id)
• 团队(id、名称)
• 结果(id、game_id、player.id、位置)
• 游戏(id，位置)
• 分数(位置，得分)

作为follows

我想制作一个类似 this 的排名表

我很困惑如何总结每个玩家的分数。到目前为止，这是我的代码:

SELECT Players.name, Teams.name, Results.position FROM Players
INNER JOIN Teams ON Players.team_id = Teams.id
JOIN Results ON Player.id = Results.player_id
JOIN Scores ON Results.position = Scores.scores

有什么想法吗？

Answer 1

SELECT Players.name, 
       Teams.name,
       sum(Results.position * Scores.scores) as scores
FROM Players
JOIN Teams ON Players.team_id = Teams.id
JOIN Results ON Player.id = Results.player_id
JOIN Scores ON Results.position = Scores.scores
GROUP BY Players.name, Teams.name