获取PHP MYSQL排名查询以基于总得分求和 [英] Getting PHP MYSQL ranking query to rank based on total sum of score

问题描述

CREATE TABLE `players` (
 `pid` int(2) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
`score` int(2) NOT NULL,
`game` varchar(20) NOT NULL,
PRIMARY KEY (`pid`),
UNIQUE KEY `pid` (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


INSERT INTO `players` (`pid`, `name`, `score`, `game`) VALUES
(1, 'Samual', 25, 'aa'),
(2, 'Vino', 20, 'aa'),
(3, 'John', 20, 'aa'),
(4, 'Samual', 22, 'bb'),
(5, 'Samual', 21, 'aa'),
(6, 'Vino', 24, 'aa'),
(7, 'John', 25, 'aa'),
(8, 'Vino', 26, 'cc'),
(9, 'John', 23, 'cc'),
(10, 'John', 19, 'aa'),
(11, 'Vino', 20, 'aa'),
(12, 'Samual', 20, 'aa');

在上表中，我希望此查询以该球员在比赛中玩的特定游戏中获得的得分总和为基础，以降序获取该球员的排名.

In the above table, i want this query to fetch the ranking of a player in descending order base on the sum of the scores gained by that player in a specific game played in the match.

SELECT pid, name, SUM(score) as score, game, rank
FROM (
SELECT pid, name, score, game,
@curRank := IF(@prevRank = score, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := score
FROM player p, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
WHERE game='aa'
ORDER BY score DESC
) s WHERE name ='John'

但是查询未以假定的格式输出结果，我希望查询总结特定游戏中所有玩家的得分，并给出该玩家的排名并考虑平局情况

but the query is not outputting the result in a supposed format, i want the query to sum up all the players scores in a particular game and give the rank of that player and also considering the tie situations

谢谢.

推荐答案

您需要在子查询中进行汇总，然后使用变量获取排名:

You need to do the aggregation in a subquery and then use the variables to get the rank:

select pid, name, game, score, (@rn := @rn + 1) as rank
from (select pid, name, game, SUM(score) as score
      from player
      where game = 'aa'
      group by pid, game
     ) p cross join
     (select @rn := 0) vars
order by score desc;

这篇关于获取PHP MYSQL排名查询以基于总得分求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！