php mysql查询求和_获取PHP MYSQL排名查询以基于总得分求和

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

thank you.

解决方案

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;