根據另一個表中定義的範圍更新表中的值

我有兩個表definitions，並history具有以下架構：

create table definitions (
   id UNIQUEIDENTIFIER,
   revision INT,
   majorVersion INT
 );

create table history (
  id UNIQUEIDENTIFIER,
  revision INT
  );

我想history用另一列更新表，majorVersion其中值範圍在表中定義definitions。例如，如果記錄definitions如下所示：

+--------------------------------------+-----------+---------------+
|                  id                  |  revision |  majorVersion |
+--------------------------------------+-----------+---------------+
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         2 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         4 |             2 |
+--------------------------------------+-----------+---------------+

那麼更新後的表格history應該是這樣的：

+--------------------------------------+-----------+---------------+
|                  id                  |  revision |  majorVersion |
+--------------------------------------+-----------+---------------+
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         1 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         2 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         3 |             2 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         4 |             2 |
+--------------------------------------+-----------+---------------+

這是一個具有更多行數的 SQL Fiddle。該表definitions可能包含數千個不同的id和多個majorVersion。隨後，表history可以包含可以包含接近一百萬行。

我想讓查詢盡可能快和優化。一種可能的解決方案是使用這樣的東西：

update history
set majorVersion = (
 select top 1 majorVersion
 from definitions
 where definitions.id = history.id
 and definitions.revision >= history.revision
 order by definitions.majorVersion
 )
where history.majorVersion = 0; 

但是這樣做的問題是我們正在查詢表中每一行的行history（與 相比可能非常大definitions）。關於如何改進這一點的任何建議？

兩種解決方案之一

解決方案 1

如果您能夠將工作拆分為兩個更新語句，請首先更新所有匹配的修訂

UPDATE h
set h.majorVersion = d.majorVersion
FROM dbo.history h
INNER JOIN
dbo.definitions d
ON d.id = h.id
and d.revision = h.revision;

然後使用 aCTE和視窗函式來更新majorVersion更新表上仍然為 0 的

;WITH CTE
AS
(
SELECT ID,revision, MAX(majorVersion) OVER (PARTITION BY Value2) as majorVersionUpdated,majorVersion
FROM
(
   SELECT ID, majorVersion,revision
       ,COUNT(case when majorVersion = 0 then NULL else majorVersion END) OVER (ORDER BY ID DESC,revision desc) AS Value2
   FROM dbo.history
) a
)

UPDATE CTE
SET majorVersion = majorVersionUpdated
WHERE majorVersion = 0;

SELECT * FROM dbo.history;

像這樣的索引可能會有所幫助

CREATE INDEX IX_id_revision
on dbo.history(id,revision)
include(majorVersion)

結果

SELECT * FROM dbo.history;

id                                 revision majorVersion
9F717823-B9CA-4C7B-97F9-7770AAAFB468    1   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    2   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    3   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    4   2
9F717823-B9CA-4C7B-97F9-7770AAAFB468    5   2
9F717823-B9CA-4C7B-97F9-7770AAAFB468    6   3
9F717823-B9CA-4C7B-97F9-7770AAAFB468    7   3
546EF185-54AC-4AF8-82C6-61EFA3202353    1   1
546EF185-54AC-4AF8-82C6-61EFA3202353    2   2
546EF185-54AC-4AF8-82C6-61EFA3202353    3   2

SQL小提琴

---

解決方案 2，仍然有兩個更新，但有一個CROSS APPLY和一個自我加入。自聯接並不理想。

首先更新所有匹配的修訂

UPDATE h
set h.majorVersion = d.majorVersion
FROM dbo.history h
INNER JOIN
dbo.definitions d
ON d.id = h.id
and d.revision = h.revision;

然後做一個自我加入以將其他majorVersions更新為下一個不為0的版本

UPDATE H 
SET h.majorVersion = h2.majorVersion
FROM dbo.history h
CROSS APPLY(
SELECT MIN(CASE WHEN h2.majorVersion = 0 
          THEN NULL 
          ELSE h2.majorVersion END) as majorVersion 
FROM
dbo.history h2 
WHERE h2.id = h.id 
AND h2.revision >= h.revision
) h2
WHERE h.majorVersion = 0;


SELECT * from dbo.history;

SQLFiddle

BEGIN TRANSACTION ... COMMIT TRANSACTION如果必須在一批中執行，則必須添加更新。最快的方法將取決於其他因素，例如您的索引和數據，而不是一個範例可以顯示的。YMMV

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