Transact-SQL 刪除最舊的 N 筆資料

本文將說明在 SQL Server 資料庫中，如何刪除最舊的前 N 筆資料 (依時間順序)。



先建立測試 Table：內含 10 筆資料，時間欄位資料範圍是由 2012/07/02 的 00:01 ~ 00:10 (每分鐘 1 筆，共 10 筆)。

CREATE TABLE TEST (SN INT, LOG_TIME DATETIME)

INSERT INTO TEST VALUES(1,  '2012/07/02 00:01:00')
INSERT INTO TEST VALUES(2,  '2012/07/02 00:02:00')
INSERT INTO TEST VALUES(3,  '2012/07/02 00:03:00')
INSERT INTO TEST VALUES(4,  '2012/07/02 00:04:00')
INSERT INTO TEST VALUES(5,  '2012/07/02 00:05:00')
INSERT INTO TEST VALUES(6,  '2012/07/02 00:06:00')
INSERT INTO TEST VALUES(7,  '2012/07/02 00:07:00')
INSERT INTO TEST VALUES(8,  '2012/07/02 00:08:00')
INSERT INTO TEST VALUES(9,  '2012/07/02 00:09:00')
INSERT INTO TEST VALUES(10, '2012/07/02 00:10:00')

GO

假設欲刪除最舊的 3 筆資料：
先取得第 3 舊資料的時間點，並儲存於變數 @BOUNDARY_DATETIME；再依此變數值刪除時間小於等於該值的所有資料。

DECLARE @BOUNDARY_DATETIME DATETIME

SELECT TOP 1 @BOUNDARY_DATETIME = A.LOG_TIME
  FROM (SELECT DISTINCT TOP 3 LOG_TIME
          FROM TEST
         ORDER BY LOG_TIME ASC) A
 ORDER BY A.LOG_TIME DESC
 
DELETE FROM TEST 
 WHERE LOG_TIME <= @BOUNDARY_DATETIME
 
SELECT * FROM TEST 

GO

上述程式碼以亦可採用較簡潔的寫法：

DELETE FROM TEST 
 WHERE LOG_TIME <= (SELECT TOP 1 A.LOG_TIME
                      FROM (SELECT DISTINCT TOP 3 LOG_TIME
                              FROM TEST
                             ORDER BY LOG_TIME ASC) A
                     ORDER BY A.LOG_TIME DESC) 
 
SELECT * FROM TEST 

GO

使用 SQL Server Express 的讀者，可參考這篇文章以進行資料庫定期刪除作業。