Update 語句中的視窗函式

是否有解決方法在更新語句中使用視窗函式

您可以使用程式碼填充樣本/原始表

Create table #table(
key1 Char(5),
date1 Date,
date2 Date
);

Insert into #table 
Values ('abc','2019-07-22',NULL);
Insert into #table Values ('abc','2019-07-23',NULL);
Insert into #table Values ('def','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-23',NULL);

我想按 key1 進行分區和排序 - 檢查 date2 中的 NULL - 如果 max(date1) over (key1 order by key1 分區) = date1 並更改為 max(date1) over (key1 order by key1 分區)，則保留 NULL如果上述條件不成立。順便說一下，這是一個數據保險庫的概念。

原來的：

必需的：

我嘗試了以下程式碼：

UPDATE #table
SET date2 = CASE WHEN MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) = date1 
 THEN NULL 
ELSE MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) END
WHERE date2 IS NULL ;

我收到一條錯誤消息：

消息 4108，級別 15，狀態 1

視窗函式只能出現在 SELECT 或 ORDER BY 子句中。

錯誤消息定義了問題：您不能在賦值 ( SET) 中直接使用視窗函式。您可以使用 CTE 拉出聚合，然後將更新應用到 CTE（將其推回表中）。

;WITH t AS 
(
 SELECT key1, date1, date2, 
   date1max = MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) 
 FROM #table
)
UPDATE t SET date2 = CASE date1max 
 WHEN date1 THEN NULL ELSE date1max END
WHERE date2 IS NULL;

如果任何給定鍵的日期都不是唯一的，您可能需要做一些事情來處理關係。請添加足夠的樣本數據來代表所有邊緣情況以及您希望如何處理這些情況。

或者，

UPDATE t
 SET 
     t.date2 = t1.date1
FROM #table t
    OUTER APPLY
(
   SELECT MAX(date1) date1
   FROM #table t1
   WHERE t1.key1 = t.key1
         AND t1.date1 > t.date1
) t1;

或者

UPDATE t
 SET 
     t.date2 = t1.date1
FROM #table t
    OUTER APPLY
(
   SELECT TOP 1 date1
   FROM #table t1
   WHERE t1.key1 = t.key1
         AND t1.date1 > t.date1
   ORDER BY t1.date1 DESC
) t1;

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