幫助解決棘手的更新聲明

我已經嘗試以我能想到的所有可能方式編寫此更新語句，但我要麼最終產生無效結果，要麼遇到語法障礙。

我有兩個表變數：

DECLARE
  @Measurements TABLE(Precidence int, -- the relative scale of a measurement
                      Measurement varchar(max), -- Grams, Kilograms, Liters, etc
                      MeasurementType varchar(max)); -- Weight, Volume, etc

Example:

Precidence | Measurement | MeasurementType
    2     |     G       |   Weight
    1     |     KG      |   Weight
    1     |     GAL     |   Volume
    2     |     L       |   Volume
    3     |     ML      |   Volume

DECLARE
  @Items TABLE(ItemType varchar(max), 
               Quantity float, 
               Measurement varchar(max), 
               ToMeasurement varchar(max));

該@Items表可以包含相同的多個測量值ItemType。對於每個ItemType我需要確定最大的測量值，考慮到不兼容的測量類型並更新ToMeasurement. 最終目標是將Quantity每一項轉換為表ItemType中存在的最大測量值，@Items以便可以對相同的項目ItemType求和。我已經編寫了轉換函式和求和運算。

給定下表輸入：

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    NULL
Widget   |    1     |    KG       |    NULL
Widget   |    1     |    ML       |    NULL
Widget   |    1     |    L        |    NULL

應更新為：

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    KG
Widget   |    1     |    KG       |    KG
Widget   |    1     |    ML       |    L
Widget   |    1     |    L        |    L

我已經多次重寫了更新，每次都做得很短。有一次，我的子查詢有四層深。我覺得我越來越近了，但它變得如此復雜，我無法只見樹木不見森林。我最近的嘗試更簡單，但再次產生不正確的結果：

UPDATE A
SET A.ToMeasurement = E.Measurement
FROM @Items A
    JOIN(SELECT C.ItemType, 
                D.Measurement
         FROM @Measurements B
              JOIN @Items C
              ON C.Measurement = B.Measurement
              JOIN @Measurements D
              ON D.MeasurementType = B.MeasurementType)E
    ON E.ItemType = A.ItemType;

我知道我什至沒有使用該Precidence列，這是它未能產生我正在尋找的結果的原因之一。

筆記

這是一個產生我正在尋找的結果的查詢（我認為），但我仍然不確定如何將其轉換為更新語句：

SELECT A.ItemType, 
      A.Quantity, 
      A.Measurement, 
      (SELECT TOP 1 M.Measurement FROM @Measurements M
      JOIN @Items C ON C.Measurement = M.Measurement
      WHERE M.MeasurementType = B.MeasurementType
      AND  C.ItemType = A.ItemType
      ORDER BY Precidence)ToMeasurement 
FROM @Items A
    JOIN @Measurements B
    ON A.Measurement = B.Measurement;

多次邏輯讀取對性能的影響可能是您的 UDF 造成的。

提供了一篇很棒的文章，它描述了使用內聯表值 UDF 來減少對標量 UDF 的逐行呼叫。更新：我注意到您已經在使用表值 UDF。

http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/

為了複製您的場景，我執行了以下查詢：

DECLARE
  @Measurements TABLE(Precidence int, -- the relative scale of a measurement
            Measurement varchar(max), -- Grams, Kilograms, Liters, etc
            MeasurementType varchar(max)); -- Weight, Volume, etc`

DECLARE
  @Items TABLE(ItemType varchar(max),   
           Quantity float,  
           Measurement varchar(max),   
           ToMeasurement varchar(max));

insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');  
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L'); 

insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');  
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume'); 

之後，我編譯並執行了以下更新語句：

WITH Items_CTE AS
  (
       SELECT A.ItemType, 
              A.Quantity,
              A.Measurement,  
              (SELECT TOP 1 M.Measurement FROM @Measurements M
               JOIN @Items C ON C.Measurement = M.Measurement
               WHERE M.MeasurementType = B.MeasurementType
               AND  C.ItemType = A.ItemType
                ORDER BY Precidence)ToMeasurement
      FROM @Items A
      JOIN @Measurements B
      ON A.Measurement = B.Measurement
   )  
UPDATE @Items  
SET ToMeasurement = cte.ToMeasurement  
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;

該UPDATE語句使用 CTE，結果如下：

ItemType | Quantity | Measurement | ToMeasurement  
Widget   |    1     |    G        |    KG  
Widget   |    1     |    KG       |    KG  
Widget   |    1     |    ML       |    L  
Widget   |    1     |    L        |    L

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