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屋!