postgresql Postgres：左连接 order by 并限制为 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21837589/
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
Postgres: left join with order by and limit 1
提问by Hikari
I have the situation:
我有这样的情况：
Table1 has a list of companies.
Table2 has a list of addresses.
Table3 is a N relationship of Table1 and Table2, with fields 'begin' and 'end'.
Because companies may move over time, a LEFT JOIN among them results in multiple records for each company.
由于公司可能会随着时间的推移而移动，因此它们之间的 LEFT JOIN 会导致每个公司有多个记录。
beginand endfields are never NULL. The solution to find the latest address is use a ORDER BY being DESC, and to remove older addresses is a LIMIT 1.
begin和end字段永远不会为 NULL。找到最新地址的解决方案是使用 a ORDER BY being DESC，删除旧地址的解决方案是LIMIT 1。
That works fine if the query can bring only 1 company. But I need a query that brings all Table1 records, joined with their current Table2 addresses. Therefore, the removal of outdated data must be done (AFAIK) in LEFT JOIN's ON clause.
如果查询只能带来 1 家公司，那效果很好。但我需要一个查询，将所有 Table1 记录与它们当前的 Table2 地址连接起来。因此，必须在 LEFT JOIN 的 ON 子句中删除过时的数据（AFAIK）。
Any idea how I can build the clause to not create duplicated Table1 companies and bring latest address?
知道如何构建条款以不创建重复的 Table1 公司并提供最新地址吗？
采纳答案by Hikari
I managed to solve it using Windows Function:
我设法使用 Windows 函数解决了它：
WITH ranked_relationship AS(
SELECT
*
,row_number() OVER (PARTITION BY fk_company ORDER BY dt_start DESC) as dt_last_addr
FROM relationship
)
SELECT
company.*
address.*,
dt_last_addr as dt_relationship
FROM
company
LEFT JOIN ranked_relationship as relationship
ON relationship.fk_company = company.pk_company AND dt_last_addr = 1
LEFT JOIN address ON address.pk_address = relationship.fk_address
row_number() creates an int counter for each record, inside each window based to fk_company. For each window, the record with latest date comes first with rank 1, then dt_last_addr = 1makes sure the JOIN happens only once for each fk_company, with the record with latest address.
row_number() 在基于 fk_company 的每个窗口内为每个记录创建一个 int 计数器。对于每个窗口，具有最新日期的记录排在第 1 位，然后dt_last_addr = 1确保每个 JOIN 仅发生一次fk_company，并使用具有最新地址的记录。
Window Functions are very powerful and few ppl use them, they avoid many complex joins and subqueries!
窗口函数非常强大，很少有人使用它们，它们避免了许多复杂的连接和子查询！
回答by krokodilko
Use a dependent subquery with max() function in a join condition.
Something like in this example:
在连接条件中使用具有 max() 函数的依赖子查询。
像在这个例子中的东西：
SELECT *
FROM companies c
LEFT JOIN relationship r
ON c.company_id = r.company_id
AND r."begin" = (
SELECT max("begin")
FROM relationship r1
WHERE c.company_id = r1.company_id
)
INNER JOIN addresses a
ON a.address_id = r.address_id
