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


INSERT INTO `players` (`pid`, `name`, `age`, `team`) VALUES
(1, 'Samual', 25, 'aa'),
(2, 'Vino', 20, 'bb'),
(3, 'John', 20, 'dd'),
(4, 'Andy', 22, 'cc'),
(5, 'Brian', 21, 'dd'),
(6, 'Dew', 24, 'xx'),
(7, 'Kris', 25, 'qq'),
(8, 'William', 26, 'cc'),
(9, 'George', 23, 'nn'),
(10, 'Peter', 19, 'aa'),
(11, 'Tom', 20, 'aa'),
(12, 'Andre', 20, 'aa');

在上表中,我希望此查询按其分数的降序获取玩家的排名.

SELECT pid, name, age, team, rank FROM
(SELECT pid, name, age, team,
@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := age
FROM players p, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
ORDER BY age DESC) s WHERE team='aa'

它给了我以下结果：

Name        |     Age    | Rank  
####--------------------------------   
Samual      |     25     | 2    
Tom         |     20     | 8    
Andre       |     20     | 8    
Peter       |     19     | 12  

但我希望以这种方式返回结果：

Name        |     Age    | Rank  
####--------------------------------   
Samual      |     25     | 1    
Tom         |     20     | 2    
Andre       |     20     | 2    
Peter       |     19     | 4   

因此,使查询给我一个特定组内的排名.

解决方法:

您需要将查询中的where子句移动为

SELECT pid, 
name, 
age, 
team, 
rank 
FROM (
 SELECT pid, name, age, team,
 @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
 @incRank := @incRank + 1, 
 @prevRank := age
 FROM players p, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
 WHERE team='aa'
 ORDER BY age DESC
) s