如何基于逐行日期条件关联两张表并填充默认值

现有两张数据表table1和table2，建表及插入数据的SQL如下：

CREATE TABLE table1
    (`PRIMARY_DT` date, `CUST_ID` int)
;
    
INSERT INTO table1
    (`PRIMARY_DT`, `CUST_ID`)
VALUES
    ('2012-03-02', 878 ),
    ('2012-07-02', 456 ),
    ('2012-09-02', 789 )
;


CREATE TABLE table2
    (`dt` date, `CUST_ID` int, `value` int)
;
    
INSERT INTO table2
    (`dt`, `CUST_ID`, `value`)
VALUES
    ('2012-03-08', 878, 1)
;

需求：将table2的value字段匹配到table1中，当table2的dt在table1的PRIMARY_DT的7天范围内时，取对应value值；若不满足该条件，则填充-9999，期望结果如下：

目前使用JOIN语句仅能返回满足条件的行，尝试用CASE语句填充默认值未成功，现寻求正确的SQL实现方案。

需要使用LEFT JOIN保留table1的所有行，同时通过客户ID和日期范围关联table2，最后用COALESCE函数将未匹配到的NULL值替换为-9999。

最终SQL代码： #

SELECT
    t1.PRIMARY_DT,
    t1.CUST_ID,
    COALESCE(t2.value, -9999) AS value
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.CUST_ID = t2.CUST_ID
    AND t2.dt BETWEEN t1.PRIMARY_DT AND DATE_ADD(t1.PRIMARY_DT, INTERVAL 7 DAY);

关键说明： #

1. LEFT JOIN：确保table1的每一行都会被返回，哪怕table2中没有符合条件的匹配记录。
2. 关联条件：同时匹配客户ID和日期范围（t2.dt落在t1.PRIMARY_DT至PRIMARY_DT+7天区间内），仅关联符合要求的table2数据。
3. COALESCE函数：当t2.value为NULL（无匹配记录）时，返回-9999，否则返回t2.value。

若使用的SQL方言不支持DATE_ADD，可替换为对应日期函数：

• PostgreSQL：t1.PRIMARY_DT + INTERVAL '7 days'
• SQL Server：DATEADD(day, 7, t1.PRIMARY_DT)

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