时间戳

首页 > 解决方案 > Mysql Sum 使用排名（所有玩家所有回合的最佳 x 分数）

mysql - Mysql Sum 使用排名（所有玩家所有回合的最佳 x 分数）

问题描述

  1. 请帮忙。我在这个论坛上测试了很多例子，但都没有成功。

该脚本运行良好，但我现在希望将总得分限制为最好的 14 轮。

SELECT 
RoundScoreCard.idPlayerDetails as ID,
#concat(PlayerFirstName," ",PlayerLastName)as Name,
sum(RoundScoreCardPlayerPoints) as Total 

from RoundScoreCard 

join PlayerDetails on RoundScoreCard.idPlayerDetails = PlayerDetails.idplayerdetails 
group by RoundScoreCard.idPlayerDetails 
order by Total DESC

使用此单个子查询进行测试可以正常工作；

select sum(RoundScoreCardPlayerPoints) 
FROM 
(select RoundScoreCardPlayerPoints
from RoundScoreCard
where RoundScoreCard.idPlayerDetails = @player 
order by RoundScoreCardPlayerPoints desc 
limit 14) as subquery
;

已经战斗了大约一周，修改了我能找到的每个示例，但无法让这个工作列出所有玩家。我可以展示多种尝试，但这会使其他人混淆这个问题。（我确实有一个解决方案，先对记录进行测试，然后再使用 IF 语句，但这似乎过多且时间成本高。）

  1. 还; 出于兴趣，是否可以有多个限制语句？即，如果我只希望获得所有回合中最好的 14 次成绩的前 3 名球员怎么办？

示例http://sqlfiddle.com/#!9/f393a6/3

新的一年万事如意。

好的 - 所以我在这篇文章的帮助下进步了一点；https://www.databasejournal.com/features/mysql/selecting-the-top-n-results-by-group-in-mysql.html

谢谢罗布格拉维尔

将每个玩家的最高分限制为 2

我的Sql现在如下：

Select idRoundScorecard, RoundScoreCardPlayerPoints,idPlayerDetails
 FROM
 (
   SELECT idRoundScorecard, RoundScoreCardPlayerPoints, idPlayerDetails,
   @player_score_rank := IF(@current_player = idPlayerDetails, 
                         @player_score_rank + 1, 
                         1
                      ) AS player_score_rank,
   @current_player := idPlayerDetails
   FROM RoundScoreCard
    ORDER BY idPlayerDetails, RoundScoreCardPlayerPoints DESC
 ) ranked
 WHERE player_score_rank <= 2;

现在我必须再次总结和排名？

标签： mysqlsumlimit

解决方案

这是我的解决方案http://sqlfiddle.com/#!9/4f4fe7/9

select idPlayerDetails as ID, concat(PlayerFirstName," ",PlayerLastName)as Name,sum(Points) as Points
from
(Select idRoundScorecard, RoundScoreCardPlayerPoints as Points,idPlayerDetails
 FROM
 (SELECT idRoundScorecard, RoundScoreCardPlayerPoints, idPlayerDetails,
   @player_rank := IF(@current_player = idPlayerDetails, 
                         @player_rank + 1, 
                         1
                      ) AS player_rank,
   @current_player := idPlayerDetails
   FROM RoundScoreCard
    ORDER BY idPlayerDetails, RoundScoreCardPlayerPoints DESC
 ) ranked
 WHERE player_rank <= 5 )summed
natural join PlayerDetails
group by idPlayerDetails
order by Points desc
 ;

推荐阅读