PostgreSQL FETCH FIRST ROWS WITH TIES 查询成绩排名并列前三名的学生
PostgreSQL 大约 2860 字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)
阅读 81 · 发布于 2023-11-07
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb扫描下方二维码关注公众号和小程序↓↓↓

昵称:
随便看看
换一批
-
Spring Boot 获取所有 SpringMVC Controller 路径阅读 2266
-
Android 使用 .nomedia 文件夹保存私有媒体文件阅读 4409
-
开源邮件服务器阅读 779
-
JVM:类加载器阅读 887
-
使用 trivy 扫描 Docker 镜像、K8S 集群的安全漏洞阅读 1599
-
Linux 之定时任务 crontab 无法启动阅读 4522
-
GoJS 更改 Overview 的拖动框颜色阅读 1582
-
Docker 同步宿主机时区阅读 547
-
改进版:根据一个文件内容查找另一个文件中的所有对应内容阅读 2189
-
macOS 使用国内源安装 Homebrew阅读 384