您可以创建一个派生表,其中每个document_id仅包含最新的“ updates”记录,然后针对该表加入“ documents”:
SELECT d.id, d.title, u.update_id, u."date"
FROM documents d
LEFT JOIN
-- JOIN "documents" against the most recent update per document_id
(
SELECT recent.document_id, id AS update_id, recent."date"
FROM updates
INNER JOIN
(SELECT document_id, MAX("date") AS "date" FROM updates GROUP BY 1) recent
ON updates.document_id = recent.document_id
WHERE
updates."date" = recent."date"
) u
ON d.id = u.document_id;
这将处理“未更新”的文档,如下所示:
pg=> select * from documents;
id | title
----+-------
1 | foo
2 | bar
3 | baz
(3 rows)
pg=> select * from updates;
id | document_id | date
----+-------------+------------
1 | 1 | 2009-10-30
2 | 1 | 2009-11-04
3 | 1 | 2009-11-07
4 | 2 | 2009-11-09
(4 rows)
pg=> SELECT d.id ...
id | title | update_id | date
----+-------+-----------+------------
1 | foo | 3 | 2009-11-07
2 | bar | 4 | 2009-11-09
3 | baz | |
(3 rows)