您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server COALESCE 函數進階實戰：動態 SQL、Pivot 轉置、批次 Kill Session

編輯：MySQL 資料庫相關知識

COALESCE vs ISNULL 核心差異

特性	COALESCE	ISNULL
參數個數	2~無上限	固定 2 個
回傳型別	依優先級推導 (高精度勝出)	第一個參數型別
NULL 屬性	結果視為 NULLABLE	結果視為 NOT NULL (若第二參數非 NULL)
標準相容	ANSI SQL 標準	T-SQL 專有
計算欄持久化	需加 `PERSISTED` 才能建索引	直接可建索引

進階實戰場景

1. 動態字串拼接 (替代游標/迴圈)


-- 將部門名稱以分號串聯
DECLARE @DeptList VARCHAR(MAX);
SELECT @DeptList = COALESCE(@DeptList + '; ', '') + Name
FROM HumanResources.Department
WHERE GroupName = 'Executive General and Administration';
SELECT @DeptList;  -- 結果: Document Control; Facilities; ...

2. 動態建構多條 SQL 陳述式 (批次執行)


-- 查詢 Person 結構下所有含 Name 欄位的表，插入暫存表
DECLARE @SQL VARCHAR(MAX);
CREATE TABLE #Tmp (ColName VARCHAR(500), Val VARCHAR(50));

SELECT @SQL = COALESCE(@SQL, '') + 
    'INSERT INTO #Tmp SELECT ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''', Name FROM ' + 
    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ';'
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN sys.objects o ON c.TABLE_NAME = o.name
WHERE c.COLUMN_NAME = 'Name' AND o.type = 'U' AND c.TABLE_SCHEMA = 'Person';

EXEC(@SQL);
SELECT * FROM #Tmp;
DROP TABLE #Tmp;

3. 批次 Kill 佔用資料庫的 Session (解決無法獨佔還原)


DECLARE @KillCmd VARCHAR(MAX);
SELECT @KillCmd = COALESCE(@KillCmd, '') + 'KILL ' + CAST(spid AS VARCHAR) + '; '
FROM sys.sysprocesses
WHERE dbid = DB_ID('AdventureWorks');

PRINT @KillCmd;  -- 確認後執行
-- EXEC(@KillCmd);

4. 計算欄持久化並建索引 (解決 NULLABLE 問題)


CREATE TABLE #CheckSumTest (
    ID INT IDENTITY,
    Num INT DEFAULT (RAND() * 100),
    RowCheckSum AS COALESCE(CHECKSUM(ID, Num), 0) PERSISTED PRIMARY KEY
);
-- PERSISTED 使計算欄實體化，COALESCE 確保非 NULL，可建 PRIMARY KEY

常見陷阱

所有參數皆為 NULL → 錯誤：`At least one of the arguments to COALESCE must be a non-null constant` → 確保至少一個非 NULL 常數
資料型別優先級導致隱含轉換溢位 → 显式 `CAST` 統一型別
動態 SQL 注入風險 → 善用 `QUOTENAME`、參數化 `sp_executesql`