如何在PostgreSQL中获取每个用户最后编辑的帖子？

Question

我有两个表格中的用户数据，如下：

1. USERID | USERPOSTID

2. USERPOSTID | USERPOST | LAST_EDIT_TIME

如何获取每个用户的最后编辑的文章和时间？假设每个用户有5篇文章，每篇文章至少被编辑一次。

我需要编写一个循环来遍历每个用户，找到具有MAX（LAST_EDIT_TIME）的USERPOST，然后收集值吗？我尝试使用GROUP BY，但我无法将USERPOSTID或USERPOST放入聚合函数中。谢谢。

- Kalpit

---

请查看distinct on子句。 - Abelisto

@Abelisto，这对我有用！如果您想提交一个稍微详细一些的答案，我可以将您的标记为已接受。 - Kalpit

我只是指向了文档。其他的都是你自己做的 ;) 只要记住 distinct on 对于每个组仅返回一行，即使有多行具有相同的最大 LAST_EDIT_TIME 值。在这种情况下，Andreas's solution 看起来更有效率。 - Abelisto

Answer 1

似乎这样做应该可以：

Seems like something like this should work:

create table users(
id serial primary key,
username varchar(50)
);

create table posts(
id serial primary key,
userid integer references users(id),
post_text text,
update_date timestamp default current_timestamp
);

insert into users(username)values('Kalpit');
insert into posts(userid,post_text)values(1,'first test');
insert into posts(userid,post_text)values(1,'second test');


select  *
from    users u
join    posts p on p.userid = u.id
where   p.update_date =
    ( select max( update_date )
        from    posts 
        where   userid = u.id )

fiddle: {{链接1:http://sqlfiddle.com/#!15/4b240/4/0}}

- Andreas

Answer 2

你可以在这里使用一个窗口函数：

select 
    USERID
    , USERPOSTID
from 
    USERS
    left join (
        select 
            USERID
            , row_number() over (
                partition by USERID
                order by LAST_EDIT_TIME desc) row_num
        from 
            USERPOST
    ) most_recent
    on most_recent.USERID = USERS.USERID
    and row_num = 1

- mechanical_meat