多条件左外连接时获取Table2最新创建行的PostgreSQL查询需求

看起来你需要的是从t1中筛选出type='C'的所有记录，然后左连接t2，并且对于每个匹配的t2分组（按id和date），只保留created日期最新的那一行，同时保证结果行数和t1中type='C'的总行数完全一致。下面给你两种PostgreSQL中常用的解决方案：

方法1：使用DISTINCT ON（PostgreSQL专属简洁写法） #

PostgreSQL的DISTINCT ON语法可以快速获取每个分组的首行，配合排序就能拿到最新的记录：

SELECT t1.id, t1.type, t1.date, t2.amount, t2.created
FROM t1
LEFT JOIN (
    -- 先预处理t2，按id+date分组，取每个组里created最新的行
    SELECT DISTINCT ON (id, date) id, date, amount, created
    FROM t2
    ORDER BY id, date, created DESC  -- 按created降序，保证首行是最新的
) t2 
ON t1.id = t2.id AND t1.date = t2.date
WHERE t1.type = 'C'  -- 只保留t1中type为C的行
ORDER BY t1.id;

解释： #

• 子查询里的DISTINCT ON (id, date)会将t2按id和date分组，每组只保留ORDER BY后的第一行；
• 通过created DESC排序，确保每组的第一行是created日期最新的记录；
• 外层用LEFT JOIN保证t1中所有type='C'的行都被保留，即使t2没有匹配的记录，对应的amount和created会显示NULL。

方法2：使用ROW_NUMBER()窗口函数（通用SQL写法） #

如果你需要更通用的SQL语法（比如兼容其他支持窗口函数的数据库），可以用ROW_NUMBER()来标记每组的最新行：

SELECT t1.id, t1.type, t1.date, t2.amount, t2.created
FROM t1
LEFT JOIN (
    -- 给t2的每个id+date分组的行按created降序编号
    SELECT 
        id, date, amount, created,
        ROW_NUMBER() OVER (PARTITION BY id, date ORDER BY created DESC) AS rn
    FROM t2
) t2 
ON t1.id = t2.id AND t1.date = t2.date AND t2.rn = 1  -- 只匹配编号为1的最新行
WHERE t1.type = 'C'
ORDER BY t1.id;

解释： #

• PARTITION BY id, date将t2按id和date分成多个组；
• ROW_NUMBER() OVER (...)给每个组内的行按created降序分配编号，最新的行编号为1；
• 连接时只筛选rn=1的行，同样通过LEFT JOIN保证t1的所有目标行都被保留。

这两种方法都能满足你的需求：结果行数和SELECT COUNT(*) FROM t1 WHERE type = 'C'完全一致，且每个匹配的t2记录都是对应分组里最新的那一行。

内容的提问来源于stack exchange，提问作者jdub