萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2005 TOP 子句增強:支援 UPDATE/DELETE 與變數參數化,配合 CTE 實現自訂排序

SQL Server 2005 擴充 TOP 功能:支援 UPDATE/DELETE、接受變數/運算式參數,但 不支援直接指定 ORDER BY。若需「更新/刪除前 N 筆依特定排序」,需搭配 **CTE (Common Table Expression)** 實現。

基本語法

-- SELECT (舊版支援)
SELECT TOP (10) * FROM tb ORDER BY id;

-- UPDATE/DELETE (2005 新增)
UPDATE TOP (2) tb SET tag = 1;
DELETE TOP (2) FROM tb;

-- 參數化
DECLARE @n INT = 3;
SELECT TOP (@n) * FROM tb;
UPDATE TOP (@n) tb SET tag = 1;

缺口:無法直接 ORDER BY

UPDATE TOP (2) tb SET tag = 1 ORDER BY id DESC ❌ 語法錯誤。

解法:CTE + TOP + ORDER BY

CTE 內部可用 TOP ... ORDER BY 篩選目標列,外層對 CTE 更新/刪除,**異動會直接反映到底表**。

完整示範腳本

USE tempdb;
GO
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
CREATE TABLE tb (id INT IDENTITY(1,1), name VARCHAR(10), tag INT DEFAULT 0);
INSERT INTO tb (name) VALUES ('a'),('b'),('c'),('d'),('e');

-- 1. 更新前兩列 (依 ID 遞增,預設順序)
UPDATE TOP (2) tb SET tag = 1;
SELECT * FROM tb;  -- id 1,2 tag=1

-- 2. 更新後兩列 (依 ID 遞減) → 需 CTE
;WITH t AS (SELECT TOP (2) * FROM tb ORDER BY id DESC)
UPDATE t SET tag = 1;
SELECT * FROM tb;  -- id 4,5 tag=1

-- 3. 刪除前兩列
DELETE TOP (2) FROM tb;
SELECT * FROM tb;  -- 剩 id 3,4,5

-- 4. 刪除後兩列 (依 ID 遞減)
;WITH t AS (SELECT TOP (2) * FROM tb ORDER BY id DESC)
DELETE FROM t;
SELECT * FROM tb;  -- 剩 id 3

進階應用:獨佔查詢 (單一消費者模式)

結合 OUTPUT 子句與 UPDATE TOP (1),可實作高併發下的「搶單/任務領取」無鎖模式:

DECLARE @task TABLE (id INT, data VARCHAR(100));
INSERT @task VALUES (1,'A'),(2,'B'),(3,'C');

UPDATE TOP (1) @task
SET data = 'PROCESSING_' + data
OUTPUT inserted.id, inserted.data  -- 立即回傳被領取的任務
WHERE data NOT LIKE 'PROCESSING_%';

此模式適用於佇列處理、分散式鎖、秒殺庫存扣減等高併發場景,避免 SELECT ... FOR UPDATE 的鎖爭用。

copyright © 萬盛學電腦網 all rights reserved