SQL中LEFT JOIN如何按条件筛选出最新时间对应的单条记录

你可以通过窗口函数ROW_NUMBER()对时间戳子查询的记录按创建时间倒序排序，筛选出每个匹配ID对应的最新记录即可，修改后的完整SQL如下：

SELECT
    distinct a.transaction_id,
    b.currency_id,
    h.SIG_CURRENCY_CODE,
    b.charge_id,
    c.charge_type_id,
    d.charge_group,
    e.account_name,
    f.sig_entity_label,
    e.trading_userid,
    e.exchange_id,
    e.tradeable_instr_name,
    g.underlying_sym_bloomberg,
    g.fut_expiration_date,
    e.trade_date,
    -- 若需要返回格式化后的时间可取消下方注释
    -- timestamp_table.timeformated,
    a.rate * a.basis_value AS dma_fee,
    SUM(A.RATE * A.BASIS_VALUE) OVER () as DMA_FEE_TOTAL
FROM
    repldoadm.ire_estimate_trans_map       a
    LEFT JOIN repldoadm.ire_charges_estimate         b ON a.estimate_id = b.estimate_id
    LEFT JOIN repldoadm.ire_charges_lu               c ON b.charge_id = c.charge_id
    LEFT JOIN repldoadm.ire_charge_types_lu          d ON c.charge_type_id = d.charge_type_id
    LEFT JOIN repldoadm.vw_ire_trade_transactions    e ON a.transaction_id = e.pb_trans_id 
    LEFT JOIN (
        SELECT
            to_char(create_ts, 'HH24:MI:SS') AS timeformated,
            trans_id,
            pb_trans_id,
            ROW_NUMBER() OVER(
                PARTITION BY CASE WHEN trans_id IS NOT NULL THEN trans_id ELSE pb_trans_id END
                ORDER BY create_ts DESC
            ) AS rn
        FROM
            repldoadm.vw_ire_trade_transactions
    ) timestamp_table ON ( 
        a.transaction_id = timestamp_table.trans_id 
        OR a.transaction_id = timestamp_table.pb_trans_id
    ) AND timestamp_table.rn = 1
-- 注意：原SQL缺失h、f、g表的关联逻辑，需要补充后才能正常执行

核心改动说明 #

• 时间戳子查询中新增ROW_NUMBER()窗口函数，按照关联ID分组（优先匹配trans_id，为空则取pb_trans_id），每组内按原始创建时间create_ts倒序排序，最新的记录排序序号为1
• 左联条件中新增timestamp_table.rn = 1，仅关联每组最新的记录，彻底避免单ID匹配多条时间记录导致的重复行问题
• 排序时使用原始时间类型的create_ts而非转成字符串的timeformated，避免字符串排序出现逻辑错误
• 筛选条件放在关联的ON子句而非WHERE子句中，保留左联特性，无匹配时间记录时也不会过滤左表数据

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