php - 从mysql表中检索5个最高分的总和

Question

这个问题已经有答案了:

How to SELECT the newest four items per category? (8 个回答)

已关闭 9 年前。

我正在开发一个项目，需要从表中按降序排列的每个团队中提取前 5 名的 Score_rank 总和。这是表结构

这是结构的链接

http://kolkata-web-design.co.in/test/structure.htm

这是我正在尝试的查询

SELECT team_id AS `team` ,  (SELECT SUM(score_rank)
FROM `contest_result_total`
WHERE team = `team_id`
ORDER BY score_rank DESC
LIMIT 5
) AS `score`
FROM `contest_result_total`
GROUP BY team_id
ORDER BY `score` DESC

但它不会给出前 5 名得分，而是计算所有比赛的总和，而不仅仅是按团队 ID 分组的 5 个

谁能帮帮我。谢谢

Answer 1

SELECT team_id,sum(score_rank)
FROM contest_result_total
WHERE (SELECT COUNT(*)
      FROM contest_result_total AS c
      WHERE c.team_id = contest_result_total.team_id
        AND contest_result_total.score_rank <= c.score_rank) <= 5
GROUP BY team_id