T-SQL：将日期关联至另一列含非零值的最近日期

我来帮你搞定这个T-SQL的表关联需求，先理清楚你的规则：

• 优先匹配同ID同日期且value非0的记录
• 如果同ID同日期的value为0，则取该ID下最近的（日期不晚于当前记录日期）非0value记录
• 没有符合条件的非0记录时返回NULL

步骤1：准备测试数据（可选，用于验证） #

首先我们先创建示例表并插入你提供的数据（注意把关键字table改成t1，避免语法冲突）：

CREATE TABLE t1 (
    id INT,
    date DATE
);

CREATE TABLE t2 (
    id INT,
    date_2 DATE,
    value INT
);

INSERT INTO t1 VALUES
(1, '2018-03-20'),
(1, '2018-02-05'),
(3, '2018-03-18'),
(7, '2018-03-12');

INSERT INTO t2 VALUES
(1, '2018-03-20', 0),
(1, '2018-03-19', 100),
(1, '2018-02-05', 50),
(3, '2018-03-18', 200);

步骤2：核心查询语句 #

下面的查询完全符合你的规则，能生成你想要的结果：

SELECT 
    t1.id,
    t1.date,
    -- 按规则匹配date_2
    CASE 
        WHEN t2_match.value <> 0 THEN t2_match.date_2
        ELSE t2_latest_nonzero.date_2
    END AS date_2,
    -- 按规则匹配value
    CASE 
        WHEN t2_match.value <> 0 THEN t2_match.value
        ELSE t2_latest_nonzero.value
    END AS value
FROM t1
-- 第一步：关联同ID同日期的t2记录
LEFT JOIN t2 t2_match 
    ON t1.id = t2_match.id 
    AND t1.date = t2_match.date
-- 第二步：获取该ID下最近的非0value记录（日期不晚于当前t1.date）
OUTER APPLY (
    SELECT TOP 1 date_2, value
    FROM t2
    WHERE id = t1.id 
      AND value <> 0 
      AND date_2 <= t1.date
    ORDER BY date_2 DESC
) t2_latest_nonzero;

结果验证 #

执行上述查询后，会得到你期望的结果：

思路解释 #

1. LEFT JOIN t2_match：先找到每个t1记录对应的同ID同日期的t2记录，用于优先匹配非0值的情况。
2. OUTER APPLY子查询：对每个t1记录，筛选出同ID下所有日期不晚于当前记录日期且value非0的t2记录，然后通过ORDER BY date_2 DESC和TOP 1拿到最近的那条。
3. CASE语句：判断优先使用t2_match的非0记录，否则使用OUTER APPLY得到的最近非0记录，没有则返回NULL。

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