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

PostgreSQL About 2,860 words

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)
Views: 561 · Posted: 2023-11-07

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh