Sql-Server
重新排序表中的記錄
我正在尋找一種 SQL 解決方案來通過更新訂單列來重新排序記錄。
考慮以下資料結構：
------------------------------------ | id | type | value | order | user | ------------------------------------ | 1 | A | 123 | 1 | 1 | | 2 | B | 231 | 2 | 1 | | 3 | B | 213 | 3 | 2 | | 4 | A | 222 | 4 | 2 | | 5 | A | 22 | 5 | 3 | | 6 | B | 11 | 6 | 4 | | 7 | C | 99 | 7 | 1 | ------------------------------------使用此數據的軟體（不可修改）考慮訂單並為每個使用者應用最大訂單的價值。但是我需要首先應用 A 類記錄（對特定使用者條目具有最大順序）。訂單列是唯一的。
目前的解決方案是創建臨時表，按所需順序選擇數據並添加新的標識列。有效重建整個訂單列。像這樣：
BEGIN TRANSACTION; -- first we take type C SELECT id ,type ,value ,[ORDER] ,user INTO #tmp FROM records WHERE type = C; -- we add new identify column ALTER TABLE #tmp ADD new_order INT identify (1,1); INSERT INTO #tmp SELECT id ,type ,value ,[ORDER] ,user FROM records WHERE type = B; -- last we take type A, these will have largest `order` values INSERT INTO #tmp SELECT id ,type ,value ,[ORDER] ,user FROM records WHERE type = A; -- old update method --update records set records.order = #tmp.new_order from records --join #tmp on #tmp.id = records.id COMMIT TRANSACTION; -- new method thanks @Lennart MERGE INTO records x USING #tmp ON #tmp.id = records.id WHEN MATCHED THEN UPDATE SET records. ORDER = #tmp.new_order; DROP TABLE #tmp;但是，此解決方案鎖定表的時間過長，這在工作時間是不可接受的。
是否有可能且有效地將其拆分為每個使用者？
- 獲取使用者 1 的所有記錄
- 僅使用給定記錄的值重新排序
order（不確定如何執行此操作）- 更新記錄表
- 為下一個使用者重複
我知道整個過程會花費更長的時間，但是表的其餘部分不會被鎖定以進行其他操作？
我正在尋找一個 SQL 解決方案。
更新
我相信我沒有詳細說明。
“訂單”列是唯一的。如果有一種 SQL 方法，我很感興趣：
- 獲取每個使用者的所有記錄。例如：
select * from records where user = 1;- 使用可用值對每個使用者的記錄進行排序。在 user=1 的情況下，我們只有值 (1,2,7) 可以使用。
- 更新記錄表中的訂單列
範例的期望結果：
------------------------------------ | id | type | value | order | user | ------------------------------------ | 1 | A | 123 | 7 | 1 | | 2 | B | 231 | 2 | 1 | | 3 | B | 213 | 3 | 2 | | 4 | A | 222 | 4 | 2 | | 5 | A | 22 | 5 | 3 | | 6 | B | 11 | 6 | 4 | | 7 | C | 99 | 1 | 1 | ------------------------------------更新#2： 這就是我理論上的意思。然而，在實踐中，這將比目前解決方案鎖定表更長的時間，並且隨著時間的推移，會使對錶的所有操作變慢。
DECLARE @curr_usr INT DECLARE curr CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT user FROM records -- foreach user OPEN curr FETCH NEXT FROM curr INTO @curr_usr WHILE @@fetch_status = 0 BEGIN -- get user entries SELECT * ,row_number() OVER ( ORDER BY type ) AS new_order INTO #tmp FROM records WHERE user = @curr_usr -- sort available order values for given record set MERGE INTO #tmp USING ( SELECT oder ,row_number() OVER ( ORDER BY [ORDER] ) AS order_seq FROM records WHERE user = @curr_usr ) x ON x.order_seq = #tmp.new_order WHEN MATCHED THEN UPDATE SET #tmp. [ORDER] = x. [ORDER]; -- update main table MERGE INTO records USING #tmp ON #tmp.id = records.id WHEN MATCHED THEN UPDATE SET records. [ORDER] = #tmp. [ORDER]; FETCH NEXT FROM curr INTO @curr_usr TRUNCATE TABLE #tmp END CLOSE curr更新#3關於數據量和時間的一些說明
大約 10k 使用者大約有 600k 條記錄。
解決方案＃1（有問題）需要
〜29秒根據@lennart答案改編的解決方案需要〜27秒
根據@daniel答案改編的解決方案需要〜26秒
這是測試數據，沒有使用者和服務在上面工作，分揀機是唯一的。我相信達到 <10 秒就足以讓客戶不會注意到滯後。
看來我將不得不調整@daniel解決方案，僅限於使用者
where user = ?並為每個使用者分別呼叫它，並在呼叫之間有延遲，這樣客戶應該不會注意到任何延遲，對吧？
桌子
CREATE TABLE dbo.Records ( id integer NOT NULL CONSTRAINT [PK dbo.Records id] PRIMARY KEY CLUSTERED (id), [type] character(1) NOT NULL, [value] integer NOT NULL, [order] smallint NOT NULL, [user] integer NOT NULL, -- order is unique CONSTRAINT [UQ dbo.Records order] UNIQUE NONCLUSTERED ([order] DESC) );索引
-- type is unique per user, useful to include order CREATE UNIQUE INDEX [UQ dbo.Records user, type (order)] ON dbo.Records ([user], [type]) INCLUDE ([order]); -- useful index on user, order CREATE NONCLUSTERED INDEX [UQ dbo.Records user, order] ON dbo.Records ([user] ASC, [order] DESC);樣本數據
INSERT dbo.Records (id, [type], [value], [order], [user]) VALUES (1, 'A', 123, 1, 1), (2, 'B', 231, 2, 1), (3, 'B', 213, 3, 2), (4, 'A', 222, 4, 2), (5, 'A', 22, 5, 3), (6, 'B', 11, 6, 4), (7, 'C', 99, 7, 1);解決方案
以下與大綱中的其他答案類似（連接兩組編號的行），但它優化了連接（避免嵌套循環），並避免更改
order不被算法應用更改的行。內聯評論：WITH Existing AS ( -- Number existing rows in order, per user SELECT R.[user], R.[order], rn = ROW_NUMBER() OVER ( PARTITION BY R.[user] ORDER BY R.[order] DESC) FROM dbo.Records AS R ), New AS ( -- Number existing rows by type, per user SELECT R.[user], R.[order], rn = ROW_NUMBER() OVER ( PARTITION BY R.[user] ORDER BY R.[type] ASC) FROM dbo.Records AS R ), OptNew AS ( -- Help the optimizer see that New.rn is unique per user SELECT New.[user], [order] = MAX(New.[order]), -- Meaningless, but required New.rn FROM New GROUP BY New.[user], New.rn ) UPDATE E SET [order] = N.[order] FROM Existing AS E JOIN OptNew AS N ON N.[user] = E.[user] AND N.rn = E.rn WHERE -- Only update if changed N.[order] <> E.[order];
您可以使用合併語句（未經測試，但您應該明白）：
merge into records x using ( select id, type, value, user, row_number() over (order by type desc, user, order) as new_order from records ) y on x.id = y.id when matched then update set order = new_order;在 using 子句中，我們正在創建一個具有預期順序的新表，用於更新原始表。