SQL实现：获取Table1记录紧接的Table2对应关联记录

你的核心需求是按pt_no（发票号）关联两表，获取与Table1记录紧接的Table2记录，原SQL的问题在于仅筛选了日期符合条件的记录，但没有提取出“紧接”的那一条（即同一发票下时间上最接近的匹配记录），导致返回结果不符合预期。结合你的示例数据，目标是找到Table2支付记录对应的同一发票下最后一条早于支付日期的评论记录，以下是高效的解决方案：

方案一：使用窗口函数（适合大数据量，推荐） #

利用ROW_NUMBER()窗口函数对同一发票号下的关联记录按日期排序，筛选出排名第一的紧接记录，该方法在40万条数据的场景下性能优异，前提是给关联字段和日期字段建立索引。

针对示例场景的SQL（匹配Table2记录对应的最近Table1评论） #

WITH ranked_comments AS (
    SELECT 
        t1.cid,
        t1.cmt_date AS comment_date,
        t1.comment,
        t2.pmt_date,
        t2.payment AS pmt,
        -- 按Table2记录分组，对关联的Table1记录按日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY t2.pid ORDER BY t1.cmt_date DESC) AS rn
    FROM table2 t2
    INNER JOIN table1 t1 
        ON t1.pt_no = t2.pt_no 
        AND t1.cmt_date < t2.pmt_date
)
-- 取排名第一的记录（即最近的评论）
SELECT cid, comment_date, comment, pmt_date, pmt
FROM ranked_comments
WHERE rn = 1;

如果需求是匹配Table1记录对应的后续Table2支付 #

若你需要的是每个Table1评论对应的第一条晚于评论日期的支付记录，可使用以下SQL：

WITH ranked_payments AS (
    SELECT 
        t1.cid,
        t1.cmt_date AS comment_date,
        t1.comment,
        t2.pmt_date,
        t2.payment AS pmt,
        -- 按Table1记录分组，对关联的Table2记录按日期正序排名
        ROW_NUMBER() OVER (PARTITION BY t1.cid ORDER BY t2.pmt_date ASC) AS rn
    FROM table1 t1
    INNER JOIN table2 t2 
        ON t1.pt_no = t2.pt_no 
        AND t2.pmt_date > t1.cmt_date
)
-- 取排名第一的记录（即最早的后续支付）
SELECT cid, comment_date, comment, pmt_date, pmt
FROM ranked_payments
WHERE rn = 1;

性能优化建议 #

为适配40万条数据的规模，需建立以下索引加速关联和排序：

-- 给Table1建立发票号+评论日期的联合索引
CREATE INDEX idx_t1_ptno_cmtdate ON table1(pt_no, cmt_date);

-- 给Table2建立发票号+支付日期的联合索引
CREATE INDEX idx_t2_ptno_pmtdate ON table2(pt_no, pmt_date);

原SQL问题分析 #

你的原SQL仅通过INNER JOIN关联了所有pmt_date > cmt_date且pt_no匹配的记录，但没有筛选出“紧接”的唯一记录，若同一发票下存在多条符合日期条件的记录，会返回所有匹配项，无法得到预期的单条紧接记录。

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