基于tk_id关联两表并仅取左表行及右表最新匹配列的SQL问题

问题根源 #

• 第一个查询的问题：tb1与tb2通过tk_id关联时，单个tk_id在tb2中对应多条记录，INNER JOIN会返回所有匹配的组合，导致行数远大于tb1中符合时间条件的记录数。
• 第二个查询的问题：仅通过DISTINCT tk_id确保了每个tk_id只出现一次，但只保留了tk_id字段，无法获取tb2的其他列数据。

正确查询写法 #

要获取tb2中每个tk_id对应的最新行，需先对tb2按tk_id分组，用窗口函数标记每组的最新记录，再与tb1关联：

SELECT 
    tb1.created_at, 
    tb1.status, 
    tb2_latest.source_id, 
    tb2_latest.destination_id
FROM tb1
INNER JOIN (
    SELECT 
        *,
        -- 按tk_id分组，以modified_at降序排序，标记最新行
        ROW_NUMBER() OVER (PARTITION BY tk_id ORDER BY modified_at DESC) AS rn
    FROM tb2
) AS tb2_latest ON tb1.tk_id = tb2_latest.tk_id
WHERE 
    tb1.created_at > timezone('utc', now()) - interval '40 minutes'
    AND tb2_latest.rn = 1; -- 仅保留每组的最新行

若判断tb2最新行的依据是created_at，将ORDER BY modified_at DESC替换为ORDER BY created_at DESC即可。

如果需要保留tb1中在tb2无匹配记录的行，可改用LEFT JOIN并处理空值：

SELECT 
    tb1.created_at, 
    tb1.status, 
    COALESCE(tb2_latest.source_id, '无匹配记录') AS source_id,
    COALESCE(tb2_latest.destination_id, '无匹配记录') AS destination_id
FROM tb1
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY tk_id ORDER BY modified_at DESC) AS rn
    FROM tb2
) AS tb2_latest ON tb1.tk_id = tb2_latest.tk_id AND tb2_latest.rn = 1
WHERE 
    tb1.created_at > timezone('utc', now()) - interval '40 minutes';

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