sql - 选择时间戳单元格上的最新条目

Question

我有这两张表：

User:
=======================
  id  |  Name | Email 
=======================
  1   | User-A| a@mail
  2   | User-B| b@mail
=======================

Entry:
=================================================
  id  |  agree |       createdOn        | userId
=================================================
  1   | true   | 2020-11-10 19:22:23    |   1
  2   | false  | 2020-11-10 22:22:23    |   1
  3   | true   | 2020-11-11 12:22:23    |   1
  4   | true   | 2020-11-04 22:22:23    |   2
  5   | false  | 2020-11-12 02:22:23    |   2
================================================

我需要得到以下结果：

=============================================================
  Name    |   Email    |   agree     |       createdOn           
=============================================================
 User-A   |  a@mail    |   true      |  2020-11-11 22:22:23
 User-B   |  b@mail    |   false     |  2020-11-12 02:22:23
=============================================================

我正在运行的 Postgres 查询是：

select distinct on (e."createdOn", u.id)
u.id , e.id ,u."Name" , u.email, e.agree, e."createdOn" from "user" u
inner join public.entry e on u."id" = e."userId"
order by "createdOn" desc

但问题是它在加入后返回所有条目！我只想要单元格的最新条目createdOn。

标签： sqlpostgresqldatetimeinner-joingreatest-n-per-group

Answer 1

您需要每个用户的最新条目。为此，您需要distinct on子句中的用户 ID，而不需要其他列。这保证了每个用户在结果集中有一行。

然后，您需要将该列首先放在order by子句中，然后是createdOn desc. 这打破了联系并决定将在每个组中保留哪一行：

select distinct on (u.id) u.id , e.id ,u."Name" , u.email, e.agree, e."createdOn" 
from "user" u
inner join public.entry e on u."id" = e."userId"
order by u.id, "createdOn" desc