如何关联CUST与CUST_ADDR表，匹配父记录日期最近的子表地址？

问题背景 #

现有两张具有父子关系的历史表CUST（客户主表）和CUST_ADDR（客户地址子表），每当数据字段发生变更时，会新增一条包含新值及变更日期的记录。

CUST表数据 #

CUST_ID     CHANGE_DATE   CUST_TITLE
1             JAN 20       CEO
1             JAN 15       MANAGER
1             JAN 10       ASSOCIATE
1             JAN 5        NEWBIE

CUST_ADDR表数据 #

CUST_ID    CHANGE_DATE        ADDRESS
1            JAN 18        1 MAIN STREET
1            JAN 16        2 ELM STREET
1            JAN 3         3 PINE DRIVE

查询需求 #

以CUST表为驱动表进行关联查询，结果集包含CUST表的每一行记录，并为每条CUST记录匹配CUST_ADDR中CHANGE_DATE早于该CUST记录变更日期且最新的地址，期望结果如下：

CUST_ID     CUST_CHANGE    CUST_TITLE  ADDR_CHANGE     ADDRESS
1             JAN 20        CEO         JAN 18       1 MAIN STREET
1             JAN 15        MANAGER     JAN 3        3 PINE DRIVE
1             JAN 10        ASSOCIATE   JAN 3        3 PINE DRIVE
1             JAN 5         NEWBIE      JAN 3        3 PINE DRIVE

解决方案 #

方案1：关联子查询（通用SQL写法） #

该写法适配绝大多数数据库，通过子查询定位每个CUST记录对应的符合条件的最新地址变更记录：

SELECT
    c.CUST_ID,
    c.CHANGE_DATE AS CUST_CHANGE,
    c.CUST_TITLE,
    ca.CHANGE_DATE AS ADDR_CHANGE,
    ca.ADDRESS
FROM CUST c
LEFT JOIN CUST_ADDR ca
    ON ca.CUST_ID = c.CUST_ID
    AND ca.CHANGE_DATE = (
        SELECT MAX(CHANGE_DATE)
        FROM CUST_ADDR ca_sub
        WHERE ca_sub.CUST_ID = c.CUST_ID
        AND ca_sub.CHANGE_DATE < c.CHANGE_DATE
    );

方案2：窗口函数写法（支持窗口函数的数据库：MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数为每个客户的地址记录按变更日期倒序排名，筛选出排名第一的最新记录：

WITH ranked_addr AS (
    SELECT
        ca.CUST_ID,
        ca.CHANGE_DATE AS ADDR_CHANGE,
        ca.ADDRESS,
        ROW_NUMBER() OVER (
            PARTITION BY ca.CUST_ID
            ORDER BY ca.CHANGE_DATE DESC
        ) AS addr_rank
    FROM CUST_ADDR ca
)
SELECT
    c.CUST_ID,
    c.CHANGE_DATE AS CUST_CHANGE,
    c.CUST_TITLE,
    ra.ADDR_CHANGE,
    ra.ADDRESS
FROM CUST c
LEFT JOIN ranked_addr ra
    ON ra.CUST_ID = c.CUST_ID
    AND ra.ADDR_CHANGE < c.CHANGE_DATE
WHERE ra.addr_rank = 1;

方案3：LATERAL JOIN（支持LATERAL的数据库：PostgreSQL、SQL Server等） #

通过LATERAL JOIN直接关联每个CUST记录对应的最新符合条件的地址：

SELECT
    c.CUST_ID,
    c.CHANGE_DATE AS CUST_CHANGE,
    c.CUST_TITLE,
    ca.CHANGE_DATE AS ADDR_CHANGE,
    ca.ADDRESS
FROM CUST c
LEFT JOIN LATERAL (
    SELECT CHANGE_DATE, ADDRESS
    FROM CUST_ADDR ca
    WHERE ca.CUST_ID = c.CUST_ID
    AND ca.CHANGE_DATE < c.CHANGE_DATE
    ORDER BY CHANGE_DATE DESC
    LIMIT 1
) ca ON true;

注：如果数据库中CHANGE_DATE为字符串格式，建议先转换为日期类型再做比较，避免字符串排序逻辑错误（比如JAN 2和JAN 10的字符串排序结果不符合日期逻辑）。可使用对应数据库的日期转换函数，如TO_DATE(CHANGE_DATE, 'MON DD')（Oracle/PostgreSQL）、STR_TO_DATE(CHANGE_DATE, '%b %d')（MySQL）。

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