PG row_num over partition by

文章目录

• 构造数据
• 需求
• 查看数据
• 根据分数排序
• 根据科目分组，按分数排序
• 根据科目分组，按分数排序
• 获取每个科目的最高分
• 获取每个科目的最低分

参考：
https://blog.csdn.net/luojinbai/article/details/45078809

构造数据

create table student(id serial,name character varying,course character varying,score integer);
 insert into student (name,course,score) values('周润发','语文',89);

 insert into student (name,course,score) values('周润发','数学',99);

 insert into student (name,course,score) values('周润发','外语',67);

 insert into student (name,course,score) values('周润发','物理',77);

 insert into student (name,course,score) values('周润发','化学',87);

 insert into student (name,course,score) values('周星驰','语文',91);

 insert into student (name,course,score) values('周星驰','数学',81);

 insert into student (name,course,score) values('周星驰','外语',88);

 insert into student (name,course,score) values('周星驰','物理',68);

 insert into student (name,course,score) values('周星驰','化学',83);

 insert into student (name,course,score) values('黎明','语文',85);

 insert into student (name,course,score) values('黎明','数学',65);

 insert into student (name,course,score) values('黎明','外语',95);

 insert into student (name,course,score) values('黎明','物理',90);

 insert into student (name,course,score) values('黎明','化学',78);

需求

查看数据

postgres=# select * from student;
id |  name  | course | score
----+--------+--------+-------
 1 | 周润发 | 语文   |    89
 2 | 周润发 | 数学   |    99
 3 | 周润发 | 外语   |    67
 4 | 周润发 | 物理   |    77
 5 | 周润发 | 化学   |    87
 6 | 周星驰 | 语文   |    91
 7 | 周星驰 | 数学   |    81
 8 | 周星驰 | 外语   |    88
 9 | 周星驰 | 物理   |    68
10 | 周星驰 | 化学   |    83
11 | 黎明   | 语文   |    85
12 | 黎明   | 数学   |    65
13 | 黎明   | 外语   |    95
14 | 黎明   | 物理   |    90
15 | 黎明   | 化学   |    78
(15 rows)

根据分数排序

postgres=# select * from student;
id |  name  | course | score
----+--------+--------+-------
 1 | 周润发 | 语文   |    89
 2 | 周润发 | 数学   |    99
 3 | 周润发 | 外语   |    67
 4 | 周润发 | 物理   |    77
 5 | 周润发 | 化学   |    87
 6 | 周星驰 | 语文   |    91
 7 | 周星驰 | 数学   |    81
 8 | 周星驰 | 外语   |    88
 9 | 周星驰 | 物理   |    68
10 | 周星驰 | 化学   |    83
11 | 黎明   | 语文   |    85
12 | 黎明   | 数学   |    65
13 | 黎明   | 外语   |    95
14 | 黎明   | 物理   |    90
15 | 黎明   | 化学   |    78
(15 rows)

根据科目分组，按分数排序

postgres=# select *,row_number() over(order by score desc)rn from student;
id |  name  | course | score | rn
----+--------+--------+-------+----
 2 | 周润发 | 数学   |    99 |  1
13 | 黎明   | 外语   |    95 |  2
 6 | 周星驰 | 语文   |    91 |  3
14 | 黎明   | 物理   |    90 |  4
 1 | 周润发 | 语文   |    89 |  5
 8 | 周星驰 | 外语   |    88 |  6
 5 | 周润发 | 化学   |    87 |  7
11 | 黎明   | 语文   |    85 |  8
10 | 周星驰 | 化学   |    83 |  9
 7 | 周星驰 | 数学   |    81 | 10
15 | 黎明   | 化学   |    78 | 11
 4 | 周润发 | 物理   |    77 | 12
 9 | 周星驰 | 物理   |    68 | 13
 3 | 周润发 | 外语   |    67 | 14
12 | 黎明   | 数学   |    65 | 15
(15 rows)

根据科目分组，按分数排序

postgres=# select *,row_number() over(partition by course order by score desc)rn from student;
id |  name  | course | score | rn
----+--------+--------+-------+----
 5 | 周润发 | 化学   |    87 |  1
10 | 周星驰 | 化学   |    83 |  2
15 | 黎明   | 化学   |    78 |  3
13 | 黎明   | 外语   |    95 |  1
 8 | 周星驰 | 外语   |    88 |  2
 3 | 周润发 | 外语   |    67 |  3
 2 | 周润发 | 数学   |    99 |  1
 7 | 周星驰 | 数学   |    81 |  2
12 | 黎明   | 数学   |    65 |  3
14 | 黎明   | 物理   |    90 |  1
 4 | 周润发 | 物理   |    77 |  2
 9 | 周星驰 | 物理   |    68 |  3
 6 | 周星驰 | 语文   |    91 |  1
 1 | 周润发 | 语文   |    89 |  2
11 | 黎明   | 语文   |    85 |  3

获取每个科目的最高分

postgres=# select * from (select *,row_number() over(partition by course order by score desc)rn from student) temp where temp.rn=1;
id |  name  | course | score | rn
----+--------+--------+-------+----
 5 | 周润发 | 化学   |    87 |  1
13 | 黎明   | 外语   |    95 |  1
 2 | 周润发 | 数学   |    99 |  1
14 | 黎明   | 物理   |    90 |  1
 6 | 周星驰 | 语文   |    91 |  1
(5 rows)

--下面SQL也可以查询每门课程的最高分，但是他不能够现实其他字段的信息
postgres=# select course, max(score)  from student group by course ;
course | max
--------+-----
语文   |  91
外语   |  95
化学   |  87
物理   |  90
数学   |  99
(5 rows)

获取每个科目的最低分

postgres=# select * from (select *,row_number() over(partition by course order by score asc)rn from student) temp where temp.rn=1;
id |  name  | course | score | rn
----+--------+--------+-------+----
15 | 黎明   | 化学   |    78 |  1
 3 | 周润发 | 外语   |    67 |  1
12 | 黎明   | 数学   |    65 |  1
 9 | 周星驰 | 物理   |    68 |  1
11 | 黎明   | 语文   |    85 |  1
(5 rows)