本文介绍了PostgreSQL:返回按外键列分组的最新行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个与此表相似的表:
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:返回按外键列分组的最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！