SQL Server 2012 实现排除baseline日期及其前后天的行查询

现有process_log_temp表结构及测试数据如下：

kpl_id     Process_date         baseline_date
----------------------------------------------------
20        2018-04-12 02:00:00         NULL
21        2018-04-13 02:01:00         NULL
22        2018-04-14 02:02:00         NULL
23        2018-04-16 09:15:00         2018-04-17 10:23:00
24        2018-04-16 17:15:00         NULL
25        2018-04-17 02:00:00         NULL
26        2018-04-18 02:00:00         NULL
--------------------------------------------------

需求说明 #

筛选所有行，排除baseline_date对应的日期、该日期前一天、该日期后一天关联的所有行，预期输出如下：

kpl_id     Process_date         baseline_date
----------------------------------------------------
20        2018-04-12 02:00:00         NULL
21        2018-04-13 02:01:00         NULL
26        2018-04-18 02:00:00         NULL
--------------------------------------------------

原有SQL错误分析 #

之前的查询逻辑存在两处核心问题：

1. 关联条件用kpl_id等值匹配，只能关联到有baseline_date的行本身，无法覆盖其他同日期范围的行
2. 筛选逻辑写反了：需求是排除符合日期范围的行，原有SQL只保留了符合日期范围的行

SELECT PL.*
FROM process_log_temp PL
WHERE NOT EXISTS (
    SELECT 1
    FROM process_log_temp PL2
    WHERE PL2.baseline_date IS NOT NULL
      AND CAST(PL.Process_date AS DATE) BETWEEN DATEADD(DAY, -1, CAST(PL2.baseline_date AS DATE)) 
                                             AND DATEADD(DAY, 1, CAST(PL2.baseline_date AS DATE))
)

逻辑说明 #

• 用NOT EXISTS做子查询匹配，判断当前行的Process_date日期是否落在任意非空baseline_date的前后1天范围内，落在范围内的行直接排除
• 统一转换为DATE类型比较，避免时间部分干扰日期判断
• 不受kpl_id关联限制，可正确匹配所有符合排除条件的行

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