PostgreSQL distinct on 用法

PostgreSQL About 1,721 words

作用

distinct on先将记录按on中指定的字段分组后返回第一条。

注意

order by必须是on中指定的字段顺序,指定字段之后再添加其他字段。

如果没有order by则随机取一条。

数据准备

score分数表:name姓名、class班级、score分数。

create table if not exists score(name text, class 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);

查询各学科最高分

subject分组的第一条,并按subjectscorename排序。

select distinct on(subject) * from score order by subject, score desc, name;

输出

postgres=# select distinct on(subject) * from score order by subject, score desc, name;
 name | class | subject | score
------+-------+---------+-------
 王五 | 一班  | 数学    |   100
 张三 | 一班  | 英语    |    95
 李四 | 一班  | 语文    |   100
(3 rows)

与 distinct 区别

distinct只能将select查询的所有字段,一起分组。

与 group by 区别

group by的语句只能select对应的group by字段和聚合函数,不能select其他字段。

select subject, max(score) from score group by subject;

输出

postgres=# select subject, max(score) from score group by subject;
 subject | max
---------+-----
 语文    | 100
 英语    |  95
 数学    | 100
(3 rows)

扩展

使用窗口函数实现查询各学科最高分功能。

select * from(
    select *, row_number() over (partition by subject order by score desc, name) row_number from score
) temp
where row_number = 1;

输出

 name | class | subject | score | row_number
------+-------+---------+-------+------------
 王五 | 一班  | 数学    |   100 |          1
 张三 | 一班  | 英语    |    95 |          1
 李四 | 一班  | 语文    |   100 |          1
(3 rows)
Views: 633 · Posted: 2023-11-02

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

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

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


Today On History
Browsing Refresh