PostgreSQL ，从 2 个表中选择，但只有表 2 中的最新元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1703495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL , Select from 2 tables, but only the latest element from table 2
提问by dr. squid
Hey, I have 2 tables in PostgreSql:
嘿，我在 PostgreSql 中有 2 个表：
1 - documents: id, title
2 - updates: id, document_id, date
and some data:
和一些数据：
documents:
文件：
| 1 | Test Title |
updates:
更新：
| 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!
包括; 我在查询中需要这个的原因是我想按更新模板中的日期排序！
Edit: 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!?
编辑：这个脚本被大大简化了，所以我应该能够创建一个查询来返回任意数量的结果，但包括最新的更新日期。我正在考虑使用内部连接或左连接或类似的东西！？
采纳答案by pilcrow
You may create a derived table which contains only the most recent "updates" records per document_id, and then join "documents" against that:
您可以创建一个派生表，其中每个 document_id 仅包含最近的“更新”记录，然后根据该表加入“文档”：
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)
回答by Quassnoi
Use PostgreSQLextension DISTINCT ON:
使用PostgreSQL扩展名DISTINCT ON：
SELECT DISTINCT ON (documents.id) *
FROM document
JOIN updates
ON updates.document_id = document_id
ORDER BY
documents.id, updates.date DESC
This will take the first row from each document.idcluster in ORDER BYorder.
这将按顺序从每个document.id集群中取出第一行ORDER BY。
Test script to check:
要检查的测试脚本：
SELECT DISTINCT ON (documents.id) *
FROM (
VALUES
(1, 'Test Title'),
(2, 'Test Title 2')
) documents (id, title)
JOIN (
VALUES
(1, 1, '2006-01-01'::DATE),
(2, 1, '2007-01-01'::DATE),
(3, 1, '2008-01-01'::DATE),
(4, 2, '2009-01-01'::DATE),
(5, 2, '2010-01-01'::DATE)
) updates (id, document_id, date)
ON updates.document_id = documents.id
ORDER BY
documents.id, updates.date DESC
回答by Tometzky
select *
from documents
left join updates
on updates.document_id=documents.id
and updates.date=(select max(date) from updates where document_id=documents.id)
where documents.id=?;
It has the some advantages over previous answers:
它比以前的答案有一些优点：
- you can write document_id only in one place which is convenient;
- you can omit where and you'll get a table of all documents and their latest updates;
- you can use more broad selection criteria, for example
where documents.id in (1,2,3).
- 你可以只在一个地方写document_id，这样很方便；
- 你可以省略 where ，你会得到一个包含所有文档及其最新更新的表格；
- 您可以使用更广泛的选择标准，例如
where documents.id in (1,2,3)。
You can also avoid a subselect using group by, but you'll have to list all fields of documents in group by clause:
您还可以使用 group by 避免子选择，但您必须在 group by 子句中列出文档的所有字段：
select documents.*, max(date) as max_date
from documents
left join updates on documents.id=document_id
where documents.id=1
group by documents.id, title;
回答by EricSchaefer
From the top of my head:
从我的头顶：
ORDER BY date DESC LIMIT 1
If you really want only id 1 your can use this query:
如果你真的只想要 id 1 你可以使用这个查询：
SELECT * FROM documents,updates
WHERE documents.id=1 AND updates.document_id=1
ORDER BY date DESC LIMIT 1
http://www.postgresql.org/docs/8.4/interactive/queries-limit.html
http://www.postgresql.org/docs/8.4/interactive/queries-limit.html
回答by David Harris
This should also work
这也应该有效
SELECT * FROM documents, updates
WHERE documents.id=1 AND updates.document_id=1
AND updates.date = (SELECT MAX (date) From updates)
