SQL 實用腳本大全：刪除重複、動態 IN 列表、交易範本、跨表更新、分頁、隨機數、觸發器判斷

1. 刪除重複記錄 (保留最早一筆，假設有 PK id)

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY id) AS rn
    FROM MyTable
)
DELETE FROM cte WHERE rn > 1;

2. 取得所有使用者資料表名

SELECT name FROM sysobjects WHERE xtype = 'U' AND status >= 0;
-- 或 (SQL 2005+)
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

3. 自動產生代碼：身分識別欄 + 日期組合

CREATE PROC usp_GenCode
    @c DATETIME, @d DATETIME, @e INT
AS
BEGIN
    DECLARE @f INT;
    INSERT INTO Table1 (c, d, e) VALUES (@c, @d, @e);
    SET @f = SCOPE_IDENTITY();
    IF @@ERROR = 0
        UPDATE Table1 SET b = CAST(@f AS VARCHAR) + CONVERT(VARCHAR(12), @d, 120)
        WHERE a = @f;
END

4. 交易控制範本 (含 XACT_ABORT)

CREATE PROC usp_TransactionDemo
    @p1 INT, @p2 VARCHAR(50)
AS
BEGIN
    SET XACT_ABORT ON;  -- 任何錯誤自動回滾
    BEGIN TRAN;
        UPDATE T1 SET col = @p2 WHERE id = @p1;
        INSERT INTO T2 (col) VALUES (@p2);
        EXEC usp_OtherProc @p1;
    COMMIT TRAN;
END

5. 動態 IN 列表處理 (字串 '1,2,3' → 參數化查詢)

DECLARE @ids VARCHAR(8000) = '14501,14502,14503';
SELECT * FROM Table1
WHERE CHARINDEX(',' + CAST(id AS VARCHAR(20)) + ',', ',' + @ids + ',') > 0;
-- 或使用字串分割函數 (SQL 2016+ STRING_SPLIT)
SELECT * FROM Table1 WHERE id IN (SELECT CAST(value AS INT) FROM STRING_SPLIT(@ids, ','));

6. 跨表更新 (依另一表欄位更新本表)

BEGIN TRAN;
UPDATE A
SET A.col1 = B.col1, A.col2 = B.col2
FROM TableA A
INNER JOIN TableB B ON A.key = B.key
WHERE A.corpid = 10001;
-- COMMIT / ROLLBACK

7. 分頁查詢 (SQL 2012+ OFFSET/FETCH)

DECLARE @PageSize INT = 20, @PageNum INT = 3;
SELECT * FROM MyTable
ORDER BY id
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

8. 按月份統計 (跨年正確排序)

SELECT CONVERT(CHAR(7), CreateTime, 120) AS YearMonth, COUNT(*) AS Cnt
FROM MyTable
GROUP BY CONVERT(CHAR(7), CreateTime, 120)
ORDER BY YearMonth;

9. 判斷觸發器觸發動作 (INSERT/UPDATE/DELETE)

CREATE TRIGGER TR_DetectAction ON MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
        PRINT 'INSERT';
    ELSE IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        PRINT 'UPDATE';
    ELSE IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        PRINT 'DELETE';
END

10. 取得所有觸發器與所屬表名

SELECT OBJECT_NAME(id) AS TriggerName, OBJECT_NAME(parent_obj) AS TableName
FROM sysobjects WHERE xtype = 'TR';

11. 隨機數函數 (因 UDF 不能用 RAND，改用 VIEW 繞過)

CREATE VIEW v_Random AS SELECT RAND() AS Val;
GO
CREATE FUNCTION dbo.GetRandom(@Len INT = 4)
RETURNS INT
AS
BEGIN
    DECLARE @v FLOAT; SELECT @v = Val FROM v_Random;
    DECLARE @mul INT = POWER(10, @Len);
    RETURN CAST(@v * @mul AS INT);
END

12. 系統物件類型代碼 (sysobjects.xtype) 速查