SQL Server跨库多日期字段匹配 如何避免编写大量CASE WHEN语句

需求背景 #

现有两个表，第一个表含5个DATE字段，第二个表含10个DATE字段，字段值为1900-01-01（约定为无日期）或空值。需求是校验同ID下第一个表的任意有效日期是否与第二个表的任意有效日期匹配，原逐字段匹配写法冗余度过高，需要优化编写思路。 #

现有实现问题 #

原单字段匹配逻辑（仅校验DB1_date1与DB2_date1）代码如下：

,case
        when (len([DB1_date1]) = 0 and len([DB2_date1]) = 0) then 1
        when len([DB1_date1]) = 0 and [DB2_date1]= '1900-01-01 00:00:00.000' then 1
        when (right([DB1_date1], 4) = '0000' or right([DB2_date1] , 2) = '00' or right([DB2_date1] , 4) = '0000' or right([DB1_date1], 2) = '00') then 5
        when cast([DB1_date1] as date) = cast([DB2_date1] as date) and (len([DB1_date1]) > 0 and len([DB2_date1]) > 0) then 0
        when len([DB1_date1]) = 0 and cast([DB1_date1] as date) <> cast([DB2_date1] as date) then 2
        when [DB2_date1] = '' and cast([DB1_date1] as date) <> cast([DB2_date1]as date) then 3
        when len([DB1_date1]) > 0 and cast([DB1_date1] as date) <> cast([DB2_date1] as date) then 4
else 5
        end as DateChk

如果要覆盖所有字段组合的匹配校验，需要编写大量CASE WHEN分支，代码冗余极高，维护成本大。 #

测试用例 #

提供的测试数据如下：

DECLARE @Table_1 TABLE(ID INT, T1_Date_1 DATE, T1_Date_2 DATE, T1_Date_3 DATE)
 
INSERT INTO @Table_1
VALUES 
(1,'1900-01-01', '1900-01-01', '1900-01-01')  ,
(2,'1900-01-01', '1974-01-01', '1900-01-01')  ,
(3,'1900-01-01', '1900-01-01', '2021-01-01')  ,
(4,'1900-01-01', '2021-01-01', '1900-01-01')  

SELECT * FROM @Table_1

DECLARE @Table_2 TABLE(ID INT, T2_Date_1 DATE, T2_Date_2 DATE, T2_Date_3 DATE, T2_Date_4 DATE);
 
INSERT INTO @Table_2
VALUES 
(1,'1900-01-01', '1900-01-01', '1900-01-01', '1900-01-01')  ,
(2,'1900-01-01', '1974-01-01', '1900-01-01', '1900-01-01')  ,
(3,'2021-01-01', '1900-01-01', '1900-01-01', '1900-01-01')  ,
(4,'1900-01-01', '2014-01-01', '1900-01-01', '1900-01-01')  

SELECT * FROM @Table_2

期望输出：

• ID 1：无标记，两张表该ID下均无有效日期
• ID 2：无标记，两张表该ID下的有效日期匹配
• ID 3：无标记，两张表该ID下的有效日期匹配
• ID 4：标记为Check，两张表该ID下的有效日期不匹配

优化方案 #

采用CROSS APPLY行转列的方式，将两个表的日期字段全部转为行存储后直接做存在性校验，无需编写大量分支逻辑：

SELECT 
    t1.ID,
    CASE
        -- 两边都没有有效日期
        WHEN NOT EXISTS (SELECT 1 FROM t1_dates WHERE valid_date IS NOT NULL) 
            AND NOT EXISTS (SELECT 1 FROM t2_dates WHERE valid_date IS NOT NULL) THEN ''
        -- 存在匹配的有效日期
        WHEN EXISTS (
            SELECT 1 FROM t1_dates d1
            INNER JOIN t2_dates d2 ON d1.valid_date = d2.valid_date
        ) THEN ''
        -- 无匹配的有效日期
        ELSE 'Check'
    END AS CheckFlag
FROM @Table_1 t1
INNER JOIN @Table_2 t2 ON t1.ID = t2.ID
-- Table1所有日期字段转成行，过滤无效值
CROSS APPLY (
    SELECT valid_date
    FROM (VALUES (T1_Date_1),(T1_Date_2),(T1_Date_3)) AS v(d)
    WHERE d NOT IN ('1900-01-01') AND d IS NOT NULL
) t1_dates
-- Table2所有日期字段转成行，过滤无效值
CROSS APPLY (
    SELECT valid_date
    FROM (VALUES (T2_Date_1),(T2_Date_2),(T2_Date_3),(T2_Date_4)) AS v(d)
    WHERE d NOT IN ('1900-01-01') AND d IS NOT NULL
) t2_dates
GROUP BY t1.ID

方案优势：

• 后续新增日期字段只需在对应CROSS APPLY的VALUES列表中添加字段名即可，无需修改核心校验逻辑
• 完全适配提供的测试用例，输出与期望结果完全一致
• 兼容SQL Server 2008及以上版本，SQL Server 16可直接运行

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