PostgreSQL FETCH FIRST ROWS WITH TIES 查询成绩排名并列前三名的学生

FETCH FIRST ROWS WITH TIES

实现Top N功能。

数据准备

create table if not exists score(name text, class text, subject text, score int);
insert into score values ('张三', '一班', '语文', 90);
insert into score values ('张三', '一班', '数学', 95);
insert into score values ('张三', '一班', '英语', 95);
insert into score values ('李四', '一班', '语文', 100);
insert into score values ('李四', '一班', '数学', 80);
insert into score values ('李四', '一班', '英语', 95);
insert into score values ('王五', '一班', '语文', 90);
insert into score values ('王五', '一班', '数学', 100);
insert into score values ('王五', '一班', '英语', 90);

相同成绩不并列

说明

row_number只是帮助查看个数，可省略。

only 不并列

select *, row_number() over(order by score desc)
from score
order by score desc
offset 0 rows
fetch first 3 rows only;

输出

z-blog=# select *, row_number() over(order by score desc) from score order by score desc offset 0 rows fetch first 3 rows only;
 name | class | subject | score | row_number
------+-------+---------+-------+------------
 李四 | 一班  | 语文    |   100 |          1
 王五 | 一班  | 数学    |   100 |          2
 李四 | 一班  | 英语    |    95 |          3

相同成绩并列

说明

row_number只是帮助查看个数，可省略。

with ties 并列

select *, row_number() over(order by score desc)
from score
order by score desc
offset 0 rows
fetch first 3 rows with ties;

输出

z-blog=# select *, row_number() over(order by score desc) from score order by score desc offset 0 rows fetch first 3 rows with ties;
 name | class | subject | score | row_number
------+-------+---------+-------+------------
 李四 | 一班  | 语文    |   100 |          1
 王五 | 一班  | 数学    |   100 |          2
 李四 | 一班  | 英语    |    95 |          3
 张三 | 一班  | 数学    |    95 |          4
 张三 | 一班  | 英语    |    95 |          5
(5 rows)

扩展

使用窗口函数rank()实现。

不并列排名

select * from (select *, rank() over (order by score desc) rank
               from score
               order by score desc) temp
offset 0 rows fetch first 3 rows only;

输出

z-blog=# select * from (select *, rank() over (order by score desc) rank
z-blog(#                from score
z-blog(#                order by score desc) temp
z-blog-# offset 0 rows fetch first 3 rows only;
 name | class | subject | score | rank
------+-------+---------+-------+------
 李四 | 一班  | 语文    |   100 |    1
 王五 | 一班  | 数学    |   100 |    1
 李四 | 一班  | 英语    |    95 |    3
(3 rows)

并列排名

select * from (select *, rank() over (order by score desc) rank
               from score
               order by score desc) temp
where rank <=3;

输出

z-blog=# select * from (select *, rank() over (order by score desc) rank
z-blog(#                from score
z-blog(#                order by score desc) temp
z-blog-# where rank <=3;
 name | class | subject | score | rank
------+-------+---------+-------+------
 李四 | 一班  | 语文    |   100 |    1
 王五 | 一班  | 数学    |   100 |    1
 李四 | 一班  | 英语    |    95 |    3
 张三 | 一班  | 数学    |    95 |    3
 张三 | 一班  | 英语    |    95 |    3
(5 rows)