SQL之组内排序
# rank() over(order by)
# 建立表
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 按 age 升序给运动员排名?
注意：rank 的 over 子句中排序字段值相同的给的排名一样
select pid,name,age,rank() over(order by age) as rank_num
from players;
1
2
2
可以看到有好几个 age 相同的运动员，他们并列排在第 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;
1
2
3
2
3
# rank over(partition by,order by)
语法 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 按年龄分组，组内按分数降序排名?
select name,age,score,rank() over(partition by age order by score desc) as rank_num
from players;
1
2
2
在 GitHub 编辑此页 (opens new window)
上次更新: 2024/02/25, 12:11:11