生成差異的最有效方法

我在 SQL Server 中有一個如下所示的表：

Id    |Version  |Name    |date    |fieldA   |fieldB ..|fieldZ
1     |1        |Foo     |20120101|23       |       ..|25334123
2     |2        |Foo     |20120101|23       |NULL   ..|NULL
3     |2        |Bar     |20120303|24       |123......|NULL
4     |2        |Bee     |20120303|34       |-34......|NULL

我正在研究一個儲存過程來區分，它接受輸入數據和版本號。輸入數據具有來自 Name uptil fieldZ 的列。大多數欄位列預計為NULL，即每行通常只有前幾個欄位的數據，其餘為NULL。名稱、日期和版本構成了對錶的唯一約束。

對於給定的版本，我需要區分相對於該表輸入的數據。每一行都需要進行比較 - 一行由名稱、日期和版本標識，並且欄位列中任何值的任何更改都需要顯示在 diff 中。

更新：所有欄位不必是十進制類型。其中一些可能是 nvarchars。我希望 diff 在不轉換類型的情況下發生，儘管 diff 輸出可以將所有內容轉換為 nvarchar，因為它僅用於顯示目的。

假設輸入如下，請求的版本為 2，：

Name    |date    |fieldA   |fieldB|..|fieldZ
Foo     |20120101|25       |NULL  |.. |NULL
Foo     |20120102|26       |27    |.. |NULL
Bar     |20120303|24       |126   |.. |NULL
Baz     |20120101|15       |NULL  |.. |NULL

差異需要採用以下格式：

name    |date    |field    |oldValue    |newValue
Foo     |20120101|FieldA   |23          |25
Foo     |20120102|FieldA   |NULL        |26
Foo     |20120102|FieldB   |NULL        |27
Bar     |20120303|FieldB   |123         |126
Baz     |20120101|FieldA   |NULL        |15

到目前為止，我的解決方案是首先使用 EXCEPT 和 UNION 生成差異。然後使用 JOIN 和 CROSS APPLY 將差異轉換為所需的輸出格式。儘管這似乎可行，但我想知道是否有更清潔，更有效的方法來做到這一點。欄位數接近100個，程式碼中每個有…的地方實際上是大量的行。隨著時間的推移，輸入表和現有表都預計會很大。我是 SQL 新手，仍在嘗試學習性能調整。

這是它的SQL：

CREATE TABLE #diff
(   [change] [nvarchar](50) NOT NULL,
   [name] [nvarchar](50) NOT NULL,
   [date] [int] NOT NULL,
   [FieldA] [decimal](38, 10) NULL,
   [FieldB] [decimal](38, 10) NULL,
   .....
   [FieldZ] [decimal](38, 10) NULL
)

--Generate the diff in a temporary table
INSERT INTO #diff
SELECT * FROM
(

(
   SELECT
       'old' as change,
       name,
       date,
       FieldA,
       FieldB,
       ...,
       FieldZ
   FROM 
       myTable mt 
   WHERE 
       version = @version
       AND mt.name + '_' + CAST(mt.date AS VARCHAR) IN (SELECT name + '_' + CAST(date AS VARCHAR) FROM @diffInput) 
   EXCEPT
   SELECT 'old' as change,* FROM @diffInput
)
UNION

(
   SELECT 'new' as change, * FROM @diffInput
   EXCEPT
   SELECT
       'new' as change,
       name,
       date,
       FieldA, 
       FieldB,
       ...,
       FieldZ
   FROM 
       myTable mt 
   WHERE 
       version = @version 
       AND mt.name + '_' + CAST(mt.date AS VARCHAR) IN (SELECT name + '_' + CAST(date AS VARCHAR) FROM @diffInput) 
) 
) AS myDiff

SELECT 
d3.name, d3.date, CrossApplied.field, CrossApplied.oldValue, CrossApplied.newValue
FROM
(
   SELECT 
       d2.name, d2.date, 
       d1.FieldA AS oldFieldA, d2.FieldA AS newFieldA, 
       d1.FieldB AS oldFieldB, d2.FieldB AS newFieldB,
       ...
       d1.FieldZ AS oldFieldZ, d2.FieldZ AS newFieldZ,
   FROM #diff AS d1
   RIGHT OUTER JOIN #diff AS d2
   ON 
       d1.name = d2.name
       AND d1.date = d2.date
       AND d1.change = 'old'
   WHERE d2.change = 'new'
) AS d3
CROSS APPLY (VALUES ('FieldA', oldFieldA, newFieldA), 
               ('FieldB', oldFieldB, newFieldB),
               ...
               ('FieldZ', oldFieldZ, newFieldZ))
               CrossApplied (field, oldValue, newValue)
WHERE 
   crossApplied.oldValue != crossApplied.newValue 
   OR (crossApplied.oldValue IS NULL AND crossApplied.newValue IS NOT NULL) 
   OR (crossApplied.oldValue IS NOT NULL AND crossApplied.newValue IS NULL)  

謝謝！

這是另一種方法：

SELECT
 di.name,
 di.date,
 x.field,
 x.oldValue,
 x.newValue
FROM
 @diffInput AS di
 LEFT JOIN dbo.myTable AS mt ON
   mt.version = @version
   AND mt.name = di.name
   AND mt.date = di.date
 CROSS APPLY
 (
   SELECT
     'fieldA',
     mt.fieldA,
     di.fieldA
   WHERE
     NOT EXISTS (SELECT mt.fieldA INTERSECT SELECT di.fieldA)

   UNION ALL

   SELECT
     'fieldB',
     mt.fieldB,
     di.fieldB
   WHERE
     NOT EXISTS (SELECT mt.fieldB INTERSECT SELECT di.fieldB)

   UNION ALL

   SELECT
     'fieldC',
     mt.fieldC,
     di.fieldC
   WHERE
     NOT EXISTS (SELECT mt.fieldC INTERSECT SELECT di.fieldC)

   UNION ALL

   ...
 ) AS x (field, oldValue, newValue)
;

這是它的工作原理：

1. 這兩個表使用外連接連接，@diffInput位於外側以匹配您的右連接。
2. 連接的結果是使用 CROSS APPLY 有條件地取消透視的，其中“有條件地”意味著每對列都單獨測試並僅在列不同時返回。
3. 每個測試條件的模式

NOT EXISTS (SELECT oldValue INTERSECT SELECT newValue)

相當於你的

oldValue != newValue
OR (oldValue IS NULL AND newValue IS NOT NULL)
OR (oldValue IS NOT NULL AND newValue IS NULL)

只會更簡潔。您可以在 Paul White 的文章Undocumented Query Plans: Equality Comparisons中詳細了解 INTERSECT 的這種用法。

換個說法，既然你說，

隨著時間的推移，輸入表和現有表都預計會很大

您可能需要考慮將用於輸入表的表變數替換為臨時表。Martin Smith 有一個非常全面的答案，探討了兩者之間的差異：

• SQL Server 中的臨時表和表變數有什麼區別？

簡而言之，表變數的某些屬性，例如缺少列統計資訊，可能會使它們對您的方案的查詢優化器友好性不如臨時表。

引用自：https://dba.stackexchange.com/questions/111223