如何结合Left join与window函数实现基于日期范围的SQL表列值修改？

首先明确核心逻辑：要为表A的每一行，判断其class_id和prop_id在表B中是否存在对应的日期范围，能完全覆盖给定的年月（即当月第一天 ≤ date_from 且 date_to ≥ 当月最后一天）。如果存在则保留原prop_id，否则设为NULL。

方法一：分组过滤 + 左连接 #

这种方法逻辑直观，先预处理表B筛选出符合条件的(class_id, prop_id)对，再和表A关联：

-- 定义目标年月参数，替换为实际需求值
DECLARE @year INT = 2022, @month INT = 5;

-- 计算目标年月的起始和结束日期
WITH target_month AS (
    SELECT 
        DATEFROMPARTS(@year, @month, 1) AS start_date,
        EOMONTH(DATEFROMPARTS(@year, @month, 1)) AS end_date
),
-- 筛选表B中能覆盖目标年月的记录，按(class_id, prop_id)分组标记有效性
valid_prop AS (
    SELECT 
        b.class_id,
        b.prop_id,
        -- 只要该(class_id, prop_id)有一行满足条件，就标记为有效
        MAX(CASE 
            WHEN b.date_from <= tm.start_date AND b.date_to >= tm.end_date
            THEN 1 
            ELSE 0 
        END) AS is_valid
    FROM TableB b
    CROSS JOIN target_month tm
    GROUP BY b.class_id, b.prop_id
)
-- 左连接表A和有效记录，生成最终结果
SELECT 
    a.class_id,
    CASE WHEN vp.is_valid = 1 THEN a.prop_id ELSE NULL END AS prop_id
FROM TableA a
LEFT JOIN valid_prop vp 
    ON a.class_id = vp.class_id 
    AND a.prop_id = vp.prop_id;

方法二：结合窗口函数实现 #

如果需要用窗口函数（比如应对更复杂的分区逻辑），可以通过PARTITION BY对每个(class_id, prop_id)分区，计算该组内是否存在符合条件的记录：

DECLARE @year INT = 2022, @month INT = 5;

WITH target_month AS (
    SELECT 
        DATEFROMPARTS(@year, @month, 1) AS start_date,
        EOMONTH(DATEFROMPARTS(@year, @month, 1)) AS end_date
),
b_with_window AS (
    SELECT 
        b.class_id,
        b.prop_id,
        -- 窗口函数：按(class_id, prop_id)分区，判断该组是否有有效记录
        MAX(CASE 
            WHEN b.date_from <= tm.start_date AND b.date_to >= tm.end_date
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY b.class_id, b.prop_id) AS has_valid_range
    FROM TableB b
    CROSS JOIN target_month tm
)
SELECT 
    a.class_id,
    -- 检查表B中是否存在对应有效记录
    CASE WHEN EXISTS (
        SELECT 1 
        FROM b_with_window bw 
        WHERE bw.class_id = a.class_id 
          AND bw.prop_id = a.prop_id 
          AND bw.has_valid_range = 1
    ) THEN a.prop_id ELSE NULL END AS prop_id
FROM TableA a;

结果验证（对应示例输入） #

• class_id=12：表B中aa_13的日期范围2022-02-15至2022-12-10完全覆盖2022-05，保留aa_13。
• class_id=13：表B中无对应ab_21的记录，返回NULL。
• class_id=22：表B中ac_11的起始日期2022-05-12晚于2022-05-01，无法覆盖整个5月，返回NULL。
• class_id=53：表B中bb_32的结束日期2022-03-19早于2022-05-01，无法覆盖，返回NULL。
• class_id=48：表B中ac_57的结束日期2022-05-07早于2022-05-31，无法覆盖整个5月，返回NULL。

两种方法均可得到预期结果，可根据实际场景选择。

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