SQL 2017-比较两个表可以为null的两个表之间的值

Question

i具有以下数据：

CREATE TABLE TestSource (
    InstrumentID int,
    ProviderID int,
    KPI1 int,
    Col2 varchar(255),
    KPI3 int
    );
CREATE TABLE TestTarget (
    InstrumentID int,
    ProviderID int,
    KPI1 int,
    Col2 varchar(255),
    KPI3 int 
    );
INSERT INTO TestSource (InstrumentID,ProviderID,KPI1,Col2,KPI3)
VALUES  (123, 27, 1, 'ABC', 10.0 ),
            (1234, 27, 2, 'DEF', 10.0 ),
            (345, 27, 1, NULL, 0.00 );
INSERT INTO TestTarget (InstrumentID,ProviderID,KPI1,Col2,KPI3)
VALUES  (123, 27, 1, 'ABC', 10.0 ),
            (1234, 27, 2, 'DEF', 10.0 ),
            (345, 27, 1, 'ABC', 0.0 );

我正在尝试比较表之间的值。这是我目前正在使用的查询逻辑：

DECLARE @Result NVARCHAR(max)
;WITH 
compare_source (InstrumentID,ProviderID,
/*** Source columns to compare ***/
            Col1Source, Col2Source,Col3Source
)
as (
              select     InstrumentID
                        ,ProviderID
                        ,KPI1
                        --,ISNULL(Col2,'NA') as Col2
                        ,Col2
                        ,KPI3
              from TestSource
              group by
                         InstrumentID
                        ,ProviderID
                        ,KPI1
                        ,Col2
                        ,KPI3
),
compare_target (InstrumentID,ProviderID,
/*** Target columns to compare ***/
            Col1Target,Col2Target,Col3Target
)
as 
(
            select
                     InstrumentID
                    ,ProviderID
                    ,KPI1
                    --,1
                    ,Col2
                    ,KPI3

            from TestTarget
            group by
                     InstrumentID
                    ,ProviderID
                    ,KPI1
                    ,Col2
                    ,KPI3
)
    SELECT @Result = STRING_AGG ('InstrumentID = ' + CONVERT(VARCHAR,InstrumentID)
         + ', Col1: ' + CONVERT(VARCHAR,Col1Source) + ' vs ' + CONVERT(VARCHAR,Col1Target)
        + ', Col2: ' + CONVERT(VARCHAR,Col2Source) + ' vs ' + CONVERT(VARCHAR,Col2Target)
        + ', Col3: ' + CONVERT(VARCHAR,Col3Source) + ' vs ' + CONVERT(VARCHAR,Col3Target) 
    , CHAR(13) + CHAR(10)
    )
FROM 
(
            select 
                     s.InstrumentID
                    ,s.Col1Source
                    ,t.Col1Target
                    ,s.Col2Source
                    ,t.Col2Target
                    ,s.Col3Source
                    ,t.Col3Target 
            from compare_source s
            left join compare_target t on t.InstrumentID = s.InstrumentID and t.ProviderID = s.ProviderID
            where not exists
            (
               select 1 from compare_target t where
                s.InstrumentID = t.InstrumentID AND 
              ( s.Col1Source   = t.Col1Target ) OR (ISNULL(s.Col1Source, t.Col1Target) IS NULL)  AND
              ( s.Col2Source   = t.Col2Target ) OR (ISNULL(s.Col2Source, t.Col2Target) IS NULL)  AND
              ( s.Col3Source   = t.Col3Target ) OR (ISNULL(s.Col3Source, t.Col3Target) IS NULL) 
        )
) diff
PRINT @Result

当我的表中没有空值时，比较效果很好。但是，一旦我尝试将nulls插入两个表，我的比较逻辑就会崩溃，并且没有考虑到表值之间的差异。

我知道我可以轻松地在我的各个选择中的列上做一个iSNULL条款。

我也尝试了比较逻辑中的以下逻辑，而没有成功：

            (
                   select 1 from compare_target t where
                    s.InstrumentID = t.InstrumentID AND 
                  ( s.Col1Source   = t.Col1Target OR (s.Col1Source IS NULL AND t.Col1Target IS NULL) ) AND
                  ( s.Col2Source   = t.Col2Target OR (s.Col2Source IS NULL AND t.Col2Target IS NULL) ) AND
                  ( s.Col3Source   = t.Col3Target OR (s.Col3Source IS NULL AND t.Col3Target IS NULL) )
            )

我遇到的另一个问题是我的查询无法区分数据格式（例如，它的值为0.00等于0.0）

）

我不确定我缺少什么。

任何帮助我走上正确的道路的帮助。

Answer 1

我看到的两个问题是：

1. 底部的WHERE子句需要具有额外的括号，以将您的OR与您的和和和您的and相结合，以使优先顺序正确：

     select 1 from compare_target t where
       s.InstrumentID = t.InstrumentID AND 
     (( s.Col1Source   = t.Col1Target ) OR (ISNULL(s.Col1Source, t.Col1Target) IS NULL))  AND
     (( s.Col2Source   = t.Col2Target ) OR (ISNULL(s.Col2Source, t.Col2Target) IS NULL))  AND
     (( s.Col3Source   = t.Col3Target ) OR (ISNULL(s.Col3Source, t.Col3Target) IS NULL)) 
   

2. 当您进行更改时，返回的一行在col2source列中具有零值。因此，当您尝试构建要发送到String_agg的字符串时，它的中间有空。因此整个字符串将为空。因此，您需要在您的子句中的子查询中或String_agg（）....或假设您的列出的位置在您的子句中使用ISNULL。