Postgres多对一关系连接多个表并选择所有行，前提是至少有一行匹配某些criterea

Question

假设我有一个类似的模式

create table if not exists user (
id serial primary key,
name text not null
);
create table if not exists post (
id serial primary key,
user_id integer not null references user (id),
score integer not null
) 

我想运行一个查询，根据ID从用户表中选择一行，以及从发布表中引用该行的所有行，前提是发布表中至少有一行的得分大于某个数字n(例如50(。不过我不太清楚该怎么做。

Answer 1

您可以使用窗口函数。让我假设post有一个user_id列，这样表就可以绑定在一起：

select u.*
from user u join
(select p.*, max(score) over (partition by user_id) as max_score
from post p
) p
on p.user_id = u.id
where p.max_score > 50;

如果你只想要所有的分数，那么通过过滤进行聚合可能就足够了：

select u.*, array_agg(p.score order by p.score desc)
from user u join
post p
) p
on p.user_id = u.id
group by u.id
having max(p.score) > 50;