PostgreSQL选择给定ID的最新条目 [英] PostgreSQL Selecting Most Recent Entry for a Given ID

问题描述

表本质上看起来像：

序列号，ID，日期，数据，数据，数据等。

Serial-ID, ID, Date, Data, Data, Data, etc.

同一ID可以有多个行。我想创建此表的视图以在报告中使用，该视图仅显示每个ID的最新条目。它应该显示所有列。

There can be Multiple Rows for the Same ID. I'd like to create a view of this table to be used in Reports that only shows the most recent entry for each ID. It should show all of the columns.

有人可以帮助我进行SQL选择吗？谢谢。

Can someone help me with the SQL select? thanks.

推荐答案

大约有5种不同的方法，但这是一种：

There's about 5 different ways to do this, but here's one:

SELECT *
FROM yourTable AS T1 
WHERE NOT EXISTS(
    SELECT *
    FROM yourTable AS T2
    WHERE T2.ID = T1.ID AND T2.Date > T1.Date
)

还有另一个：

SELECT T1.*
FROM yourTable AS T1
LEFT JOIN yourTable AS T2 ON
(
    T2.ID = T1.ID 
    AND T2.Date > T1.Date
)
WHERE T2.ID IS NULL

一个：

WITH T AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS rn
    FROM yourTable
)
SELECT * FROM T WHERE rn = 1

好吧，我被带走了，这是我要发布的最后一个（现在） ：

Ok, i'm getting carried away, here's the last one I'll post(for now):

WITH T AS (
    SELECT ID, MAX(Date) AS latest_date
    FROM yourTable
    GROUP BY ID
)
SELECT yourTable.*
FROM yourTable
JOIN T ON T.ID = yourTable.ID AND T.latest_date = yourTable.Date

这篇关于PostgreSQL选择给定ID的最新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！