如何获取每个产品的最新文档？

Question

我有以下简化表：

Product: [uniqueId|name]
ProductAttachment: [FK productId|FK attachmentId]
Attachment: [uniqueId|createdOn|name]

产品表和附件表是n:m关系，productAttachment是中间表。不能保证createdOn对于每个附件都是唯一的。

我正在尝试获取每个产品的最新附件。我可以连接表，但我不知道如何获得每个产品的完整附件行，而不是每个产品获得多个行。这是一个大得多的查询的一部分，所以理想情况下，我希望在不更改查询的其余部分的情况下执行此操作。如果我可以从子查询中获得attachmentId，我就可以在单独的连接中使用它。

我看过一些类似的问题，比如：

SQL select only rows with max value on a column

和

Finding the most recent timestamp per event

但据我所知，这些答案不适用于n:m关系。我尝试使用聚集子查询，但我只获得了附件的创建日期，如果我将attachmentId添加到GROUP BY子句中，我将获得每个产品的多个附件。

SELECT p.UniqueId, mostRecentAttachment.created
  FROM ps.product AS p
       LEFT JOIN (SELECT pa.productId, MAX(a.created) AS created
                     FROM ps.productAttachment AS pa
                          INNER JOIN bs.attachment AS a ON pa.attachmentId = a.uniqueId
                    GROUP BY pa.productId) AS mostRecentAttachment ON mostRecentAttachment.productId = p.uniqueId
 WHERE p.active

我希望每个产品都有一行(大约有12000个产品)和最新的附件(如果产品有一个的话)。

Answer 1

demo:db<>fiddle

使用DISTINCT ON只允许过滤有序组的第一个元素。这里的组是产品。加入附件后，您可以按日期降序排列它们的附件。因此，在每个产品组中，最新的附件成为第一个。此记录由DISTINCT ON子句获取。

SELECT DISTINCT ON (p.unique_id)
    p.*,
    a.*
FROM
    products p 
JOIN product_attachment pa ON p.unique_id = pa.product_id
JOIN attachments a ON pa.attachment_id = a.unique_id
ORDER BY p.unique_id, a.created_on DESC