postgresql - 在表连接中使用聚合函数的最有效方法？

Question

我正在尝试连接两个表，其中表一中的所有记录都具有唯一 ID，而表二可以包含表一中一个 ID 的多条记录(表一中的 ID 可能没有任何表二中的相应记录)。

我只想加入表二中的最新记录。

SELECT id, MAX(date) AS DATE FROM table_two

以上返回特定 ID 的最新记录。

SELECT * FROM table_one t1 LEFT JOIN table_two t2 ON t1.id = t2.id

以上返回连接表，但也返回重复项。

如何构造连接语句以仅返回表二中的最新记录？

像这样:

SELECT * FROM table_one t1 LEFT JOIN table_two t2 ON t1.id = t2.id, MAX(t2.date) AS date GROUP BY date 

以上返回错误:错误:FROM 中的函数中不允许使用聚合函数

我知道我可以使用子查询来获取最新记录，但不确定最有效的方法。

Answer 1

你必须使用子查询。这里有两个可能的答案:

使用 row_number 和子查询

select * from(
    SELECT t1.*,row_number() over(partition by t2.id order by t2.date desc) as row_num
    FROM table_one t1
    LEFT JOIN table_two t2 ON t1.id = t2.id
)t
where t.row_num = 1

使用最大和子查询

SELECT * FROM table_one t1 
LEFT JOIN 
(
    SELECT id, MAX(date) AS DATE FROM table_two group by id
)t2
ON t1.id = t2.id