PostgreSQL ，从 2 个表中选择，但只有表 2 中的最新元素 [英] PostgreSQL , Select from 2 tables, but only the latest element from table 2

问题描述

嘿，我在 PostgreSql 中有 2 个表:

Hey, I have 2 tables in PostgreSql:

1 - documents: id, title
2 - updates: id, document_id, date

和一些数据:

文档:

| 1 | Test Title |

更新:

| 1 | 1 | 2006-01-01 |
| 2 | 1 | 2007-01-01 |
| 3 | 1 | 2008-01-01 |

所以所有更新都指向同一个文档，但更新日期不同.

So All updates are pointing to the same document, but all with different dates for the updates.

我想要做的是从文档表中进行选择，但还要包括基于日期的最新更新.

What I am trying to do is to do a select from the documents table, but also include the latest update based on the date.

这样的查询应该是什么样的?这是我目前拥有的更新，但我列出了所有更新，而不是我需要的最新更新:

How should a query like this look like? This is the one I currently have, but I am listing all updates, and not the latest one as the one I need:

SELECT * FROM documents,updates WHERE documents.id=1 AND documents.id=updates.document_id ORDER BY date

包括;我在查询中需要这个的原因是我想按更新模板中的日期排序！

To include; The reason I need this in the query is that I want to order by the date from the updates template!

这个脚本很重简化，所以我应该能够创建一个返回任何数字的查询结果，但包括最新的更新日期.我想用一个内连接或左连接什么的就这样！?

This script is heavily simplified, so I should be able to create a query that returns any number of results, but including the latest updated date. I was thinking of using a inner join or left join or something like that!?

推荐答案

您可以创建一个派生表，其中只包含每个 document_id 的最新更新"记录，然后针对该记录加入文档":

You may create a derived table which contains only the most recent "updates" records per document_id, and then join "documents" against that:

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;

这将处理未更新"的文档，如下所示:

This will handle "un-updated" documents, like so:

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)

这篇关于PostgreSQL ，从 2 个表中选择，但只有表 2 中的最新元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！