如何自连接表匹配15天内归还对应的最早借出记录？

需求：对borrow_data表进行自连接，将每条归还记录（type='RETURNED'）匹配到其15天内发生的最早借出记录（type='BORROWED'）；若借出记录距归还记录超过15天则跳过，无符合条件的借出记录则显示NULL。

现有表结构与数据 #

基础查询语句：

SELECT
trans_numb,
type,
date,
Borrow_occur,
return_occur
FROM borrow_data;

表数据：

期望输出框架与结果 #

期望查询框架：

SELECT
r.type AS type_return,    
r.return_occur,    
r.date AS return_date,    
b.type AS type_borrow,    
b.borrow_occur,    
b.date AS borrow_date,    
Date_diff(r.date,b.date) AS date_diff   
FROM borrow_data r
LEFT JOIN borrow_data b ...

期望输出结果：

注：第3行跳过borrow_occur=3的记录（距归还记录超过15天）；第5行无符合条件的借出记录，故显示NULL。

方式一：关联子查询匹配最早记录 #

直接通过子查询定位每个归还记录对应的、符合日期条件的最早借出记录，逻辑简洁：

SELECT
    r.type AS type_return,
    r.return_occur,
    r.date AS return_date,
    b.type AS type_borrow,
    b.borrow_occur,
    b.date AS borrow_date,
    -- 不同数据库日期差函数有差异，MySQL用DATEDIFF，PostgreSQL用AGE
    DATEDIFF(r.date, b.date) AS date_diff
FROM borrow_data r
LEFT JOIN borrow_data b
    ON b.type = 'BORROWED'
    -- 精确筛选15天内的借出记录（含时间则用TIMESTAMPDIFF避免误判）
    AND TIMESTAMPDIFF(DAY, b.date, r.date) <= 15
    AND b.date <= r.date
    -- 锁定该归还记录下最早的借出记录
    AND b.date = (
        SELECT MIN(b_inner.date)
        FROM borrow_data b_inner
        WHERE b_inner.type = 'BORROWED'
        AND TIMESTAMPDIFF(DAY, b_inner.date, r.date) <= 15
        AND b_inner.date <= r.date
    )
WHERE r.type = 'RETURNED'
ORDER BY r.return_occur;

方式二：窗口函数筛选最优匹配 #

先关联所有符合条件的借出-归还对，再用ROW_NUMBER()窗口函数给候选记录排序，取最早的一条：

WITH borrow_return_pairs AS (
    SELECT
        r.type AS type_return,
        r.return_occur,
        r.date AS return_date,
        b.type AS type_borrow,
        b.borrow_occur,
        b.date AS borrow_date,
        TIMESTAMPDIFF(DAY, b.date, r.date) AS date_diff,
        -- 按归还记录分组，借出记录按日期升序排序
        ROW_NUMBER() OVER (PARTITION BY r.return_occur ORDER BY b.date ASC) AS rn
    FROM borrow_data r
    LEFT JOIN borrow_data b
        ON b.type = 'BORROWED'
        AND TIMESTAMPDIFF(DAY, b.date, r.date) <= 15
        AND b.date <= r.date
    WHERE r.type = 'RETURNED'
)
SELECT
    type_return,
    return_occur,
    return_date,
    type_borrow,
    borrow_occur,
    borrow_date,
    date_diff
FROM borrow_return_pairs
-- 取每个归还记录的最早借出记录，无匹配则保留NULL
WHERE rn = 1 OR rn IS NULL
ORDER BY return_occur;

关键说明 #

• 无需递归：本需求是日期范围匹配+取极值场景，递归仅适用于层级遍历、连续数据迭代等情况，这里完全不需要。
• 日期精度：若date字段包含时间，建议用TIMESTAMPDIFF（MySQL）或INTERVAL比较（PostgreSQL）精确计算时间差，避免因整天数差导致的误判（比如用户示例中borrow_occur=3的记录与归还记录间隔超15天，会被正确排除）。

内容的提问来源于stack exchange，提问作者M. Die K. S.