萬盛學電腦網

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

SQL Server COALESCE 函數進階應用:動態字串拼接與多薪資制計算

COALESCE:處理 NULL 的瑞士刀,不只單純取首個非空值

COALESCE(expr1, expr2, ..., exprN) 回傳參數清單中**第一個非 NULL** 的運算式。本文展示兩大進階場景:動態姓名拼接與多薪資制統一計算,並示範如何封裝為函數與計算欄優化。

場景一:人員全名動態拼接 (處理中間名缺失)

資料樣本

FirstNameMiddleNameLastName
JohnA.MacDonald
FranklinD.Roosevelt
MadonnaNULLNULL
CherNULLNULL
MaryNULLWeilage

基本用法

SELECT FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + COALESCE(LastName, '')
FROM Persons;

問題: 無中間名/姓氏時會產生多餘空格 (如 James Bond 雙空格)。

優化版:LTRIM 修正前導空格

SELECT LTRIM(FirstName + ' ' + COALESCE(MiddleName, '') + ' ') + COALESCE(LastName, '')
FROM Persons;

結果:James Bond、John A. MacDonald、Madonna、Cher、Mary Weilage。

封裝為標量函數 (可重用)

CREATE FUNCTION dbo.GetFullName 
(
    @First NVARCHAR(50), 
    @Middle NVARCHAR(50), 
    @Last NVARCHAR(50)
)
RETURNS NVARCHAR(150)
AS
BEGIN
    DECLARE @Result NVARCHAR(150);
    SELECT @Result = LTRIM(@First + ' ' + COALESCE(@Middle, '') + ' ') + COALESCE(@Last, '');
    RETURN @Result;
END;
GO

-- 調用
SELECT dbo.GetFullName('James', NULL, 'Bond');       -- James Bond
SELECT dbo.GetFullName('Cher', NULL, NULL);          -- Cher
SELECT dbo.GetFullName('John', 'F.', 'Kennedy');     -- John F. Kennedy

場景二:多薪資制統一發薪計算

薪資結構表

EmpIDHourlyWageHoursPerWeekPieceRatePiecesThisWeekWeeklySalaryCommission
118.0040NULLNULLNULLNULL
2NULLNULL4.00400NULLNULL
3NULLNULLNULLNULL800.00NULL
4NULLNULLNULLNULL500.00600.00

單一查詢統一計算

SELECT EmployeeID,
       COALESCE(HourlyWage * HoursPerWeek, 0) +
       COALESCE(PieceRate * PiecesThisWeek, 0) +
       COALESCE(WeeklySalary + Commission, 0) AS TotalPayment
FROM Payroll;

結果

EmployeeID  TotalPayment
1           720.00
2           1600.00
3           800.00
4           1100.00

持久化計算欄 (效能最優,自動維護)

ALTER TABLE Payroll
ADD TotalPayment AS 
    COALESCE(HourlyWage * HoursPerWeek, 0) +
    COALESCE(PieceRate * PiecesThisWeek, 0) +
    COALESCE(WeeklySalary + Commission, 0) PERSISTED;

-- 可直接建立索引
CREATE INDEX IX_Payroll_TotalPayment ON Payroll(TotalPayment);

此後 SELECT * FROM Payroll 即含預計算欄位,查詢零運算開銷。

進階技巧:COALESCE vs ISNULL vs CASE

特性COALESCEISNULLCASE
參數數量不限 (≥2)固定 2不限
標準相容ANSI SQL 標準T-SQL 專有ANSI SQL
回傳型別所有運算式最高優先級第一個運算式型別所有結果最高優先級
短路求值是是是
可作為計算欄是是是 (需 PERSISTED)

實務建議

  • **多參數首選 COALESCE** (標準、彈性)。
  • **雙參數簡單替換用 ISNULL** (效能極微優、語意清晰)。
  • **計算欄持久化** 將業務邏輯下推儲存層,查詢層零成本。
  • **避免在 WHERE/JOIN 條件對索引欄用 COALESCE/ISNULL** (導致掃描),改寫為 WHERE Col = @Val OR (Col IS NULL AND @Val IS NULL) 或應用層處理。
copyright © 萬盛學電腦網 all rights reserved