如何使用SQL干净地输出DIFF

Question

我正在研究解决方案，以帮助比较两个表之间的数据。说我有这两个桌子

Table: Clients_old
----------------------------------------------
ClientID    FirstName   LastName        Age
----------------------------------------------
1           John        Doe             20
2           Jane        Doe             20
Table: Clients_updated
----------------------------------------------
ClientID    FirstName   LastName        Age
----------------------------------------------
1           John        Doe             99
2           Jane        Smith           99

现在，我使用的是除了和一些丑陋的案例逻辑以输出上面的差异。

---------------------------------------------------------------------------------------------------------
ClientID    FirstName_Old   FirstName_Updated   LastName_Old    LastName_Updated    Age_Old     Age_Updated
---------------------------------------------------------------------------------------------------------
1           NULL            NULL                NULL            NULL                20          99  
2           NULL            NULL                Doe             Smith               20          99

这是一个开始，但我想清理输出。给定这两个表，SQL是否有办法获得这样的差异格式？

-----------------------------------------------------
ClientID    ColumnName      OldValue    UpdatedValue
-----------------------------------------------------
1           Age             20          99  
2           LastName        Doe         Smith
2           Age             20          99

我对如何使用C＃进行操作有一个想法，但是我很好奇是否有SQL解决方案。

Answer 1

您可以在SQL Server中取消分类。为此，我可能建议：

with co as (
      select v.*
      from clients_old co cross apply
           (values (co.client_id, co.firstname, 'firstname'),
                   (co.client_id, co.lastname, 'lastname'),
                   (co.client_id, co.age, 'age')
           ) v(client_id, val, col)
     ),
     cu as (
      select v.*
      from clients_updated cu cross apply
           (values (cu.client_id, cu.firstname, 'firstname'),
                   (cu.client_id, cu.lastname, 'lastname'),
                   (cu.client_id, cu.age, 'age')
           ) v(client_id, val, col)
     )
select co.client_id, co.col, co.val as old_value, cu.val as updated_value
from co join
     cu
     on co.client_id = cu.client_id and co.col = cu.col and
        co.val <> cu.val;

如果val可以是NULL。

请注意，此方法确实假定列类型都是字符串。有这样的方法，但是代码有些复杂。

Answer 2

戈登的解决方案将更具性能。

但是，这是一种不敏感数据类型的方法，而无需指定字段的动态。

示例或dbfiddle

Select A.ClientID
      ,C.Field
      ,OldValue = max(case when Src='Old' then Value end)
      ,NewValue = max(case when Src='New' then Value end)
 From (
        Select Src='Old',* from Clients_old
        Union All
        Select Src='New',* from Clients_new
      ) A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Field = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('Src','ClientID')
             ) C
 Group By A.ClientID,C.Field
 Having max(case when Src='Old' then Value end) <> max(case when Src='New' then Value end)
        or  count(*)=1

返回

ClientID    Field    OldValue   NewValue
1           Age      20         99
2           Age      20         99
2           LastName Doe        Smith