SQL两表关联查询时匹配距离action_taken_date最近的未来com_fur_date

方案1：窗口函数法（推荐，适配MySQL8.0+、PostgreSQL、Oracle、SQL Server等绝大多数主流数据库） #

通过窗口函数对匹配到的后续日期排序，取最近的一条：

WITH matched_records AS (
    SELECT
        a.ACTION_TAKEN_DATE,
        a.RULE_DESCRIPTION,
        a.SERVICE_ID,
        b.COM_FUR_DATE,
        b.FUR_REASON_CODE,
        b.Circuit_id,
        -- 按table1的唯一标识分组，对匹配到的后续日期升序排序
        ROW_NUMBER() OVER (
            PARTITION BY a.SERVICE_ID, a.ACTION_TAKEN_DATE, a.RULE_DESCRIPTION 
            ORDER BY b.COM_FUR_DATE ASC
        ) AS rn
    FROM `table 1` a
    LEFT JOIN `table 2` b 
        ON a.SERVICE_ID = b.CIRCUIT_ID
        -- 仅匹配晚于行动日期的未来记录
        AND b.COM_FUR_DATE > a.ACTION_TAKEN_DATE
)
SELECT
    ACTION_TAKEN_DATE,
    RULE_DESCRIPTION,
    SERVICE_ID,
    COM_FUR_DATE,
    FUR_REASON_CODE,
    Circuit_id,
    CASE WHEN COM_FUR_DATE IS NULL THEN 'No further' ELSE 'Further' END AS Further_Status
FROM matched_records
-- 取排序第一的最近未来日期，无匹配的记录也会保留
WHERE rn = 1 OR COM_FUR_DATE IS NULL;

方案2：关联子查询法（适配不支持窗口函数的低版本数据库，比如MySQL5.x） #

通过子查询直接取每个行动记录对应的最小未来日期：

SELECT
    a.ACTION_TAKEN_DATE,
    a.RULE_DESCRIPTION,
    a.SERVICE_ID,
    b.COM_FUR_DATE,
    b.FUR_REASON_CODE,
    b.Circuit_id,
    CASE WHEN b.COM_FUR_DATE IS NULL THEN 'No further' ELSE 'Further' END AS Further_Status
FROM `table 1` a
LEFT JOIN `table 2` b 
    ON a.SERVICE_ID = b.CIRCUIT_ID
    AND b.COM_FUR_DATE = (
        SELECT MIN(b2.COM_FUR_DATE)
        FROM `table 2` b2
        WHERE b2.CIRCUIT_ID = a.SERVICE_ID
        AND b2.COM_FUR_DATE > a.ACTION_TAKEN_DATE
    )

核心逻辑说明 #

• 关联时新增日期过滤条件，仅保留晚于action_taken_date的com_fur_date记录
• 对同一个行动记录匹配到的多条后续日期，取日期最小的即为最近的未来日期
• 左关联保留无后续匹配的行动记录，保证Further_Status的判断逻辑正确

注意事项 #

• 原SQL存在两处语法错误：b.FUR_REASON_CODE和b.Circuit_id之间缺少逗号，表名table 1如果实际名称包含空格需要用对应数据库的标识符包裹（MySQL用反引号`，SQL Server用方括号[]）
• 如果业务上允许匹配和行动日期同一天的记录，可以将条件中的>修改为>=
• 如果table 1存在多条SERVICE_ID、ACTION_TAKEN_DATE、RULE_DESCRIPTION完全相同的记录，建议新增table 1的唯一主键作为窗口函数的分区字段，避免分组错误

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