PostgreSQL distinct on 用法

作用

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分组的第一条，并按subject、score、name排序。

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)