MySQL之rank() over(order by)

一、准备工作

创建一个表palyers：

CREATE TABLE `players` (
  `pid` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) 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`) VALUES
(1, 'Samual', 25),
(2, 'Vino', 20),
(3, 'John', 20),
(4, 'Andy', 22),
(5, 'Brian', 21),
(6, 'Dew', 24),
(7, 'Kris', 25),
(8, 'William', 26),
(9, 'George', 23),
(10, 'Peter', 19),
(11, 'Tom', 20),
(12, 'Andre', 20);

二、使用rank() over

注意：rank的over子句中排序字段值相同的给的排名一样。

rank over(order by 列名)

1、 按age升序给运动员排名

select pid,name,age,rank() over(order by age) as rank_num from players;

 小细节：排名列叫rank_num 别名的时候as也可以省略。

可以看到有好几个age相同的运动员，他们并列排在第2。

2、查询排名为第10的学生的姓名，年龄

select name,age
from (select pid,name,age,rank() over(order by age) as rank_num from players) as rank_table --临时表rank_table
where rank_num= 10;

整个结果集为一个分组，下面我们可以指定通过partition by 给结果集进行分组，rank在每个分组内进行排名。

rank over(partition by 列名,order by 列名)

partition by用于给结果集分组。
rank在每个分组内进行排名。

改一下表，新增一列：

alter table players 
add score int;

update players set score=98 where pid=1;
update players set score=96 where pid=2;
update players set score=92 where pid=3;
update players set score=96 where pid=4;
update players set score=97 where pid=5;
update players set score=92 where pid=6;
update players set score=88 where pid=7;
update players set score=89 where pid=8;
update players set score=88 where pid=9;
update players set score=88 where pid=10;
update players set score=92 where pid=11;
update players set score=91 where pid=12;

select * from players;

1、按年龄分组，组内按分数降序排名

select name,age,score,rank() over(partition by age order by score desc) as rank_num
from players;