PostgreSQL:返回按外键列分组的最新行 [英] PostgreSQL: Returning the most recent rows grouping by a foreign key column

问题描述

我有一个与此表相似的表:

I have a table that looks similar to this:

credit
+---------+----------------+-------------+--------------+-------------------------+
| id (PK) | person_id (FK) | transaction | total credit |        date_time        |
+---------+----------------+-------------+--------------+-------------------------+
|     345 |              1 |       -1.00 |        34.50 | 2018-08-29 12:00:00.000 |
|     897 |              1 |        5.45 |        39.95 | 2018-08-29 12:34:00.000 |
|     378 |              2 |        0.01 |         0.01 | 2018-08-29 08:00:00.000 |
|     789 |              2 |       20.00 |        20.01 | 2018-08-29 09:00:00.000 |
+---------+----------------+-------------+--------------+-------------------------+

我将如何在Postgres中编写查询以仅返回表中每个唯一的person_id分组的最新行(按date_time DESC)?

How would I write a query in Postgres to return only the most recent (by date_time DESC) row grouped by each unique person_id in the table, like this?

+---------+----------------+-------------+--------------+-------------------------+
| id (PK) | person_id (FK) | transaction | total credit |        date_time        |
+---------+----------------+-------------+--------------+-------------------------+
|     897 |              1 |        5.45 |        39.95 | 2018-08-29 12:34:00.000 |
|     789 |              2 |       20.00 |        20.01 | 2018-08-29 09:00:00.000 |
+---------+----------------+-------------+--------------+-------------------------+

推荐答案

使用distinct on:

select distinct on (person_id) t.*
from t
order by person_id, date_time desc

这篇关于PostgreSQL:返回按外键列分组的最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！