推理文by阿誠

T-SQL COALESCE 範例 -------------------------------------------------------------------------------- A.標準用法 SELECT A, B, C, D, COALESCE(A, B, C) AS E FROM Student ; GO B.MSDN範例 wages 資料表有三個含員工年薪 (時薪、月薪加上分紅) 相關資訊的資料行。 不過，員工只會收到其中一種款項。 若要算出支付給所有員工的總金額，請使用 COALESCE，只接收 hourly_wage、salary 和 commission 中的非 Null 值。 複製 SET NOCOUNT ON; GO USE tempdb; IF OBJECT_ID('dbo.wages') IS NOT NULL DROP TABLE wages; GO CREATE TABLE dbo.wages ( emp_id tinyint identity, hourly_wage decimal NULL, salary decimal NULL, commission decimal NULL, num_sales tinyint NULL ); GO INSERT dbo.wages (hourly_wage, salary, commission, num_sales) VALUES (10.00, NULL, NULL, NULL), (20.00, NULL, NULL, NULL), (30.00, NULL, NULL, NULL), (40.00, NULL, NULL, NULL), (NULL, 10000.00, NULL, NULL), (NULL, 20000.00, NULL, NULL), (NULL, 30000.00, NULL, NULL), (NULL, 40000.00, NULL, NULL), (NULL, NULL, 15000, 3), (NULL, NULL, 25000, 2), (NULL, NULL, 20000, 6), (NULL, NULL, 14000, 4); GO SET NOCOUNT OFF; GO SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS money) AS 'Total Salary' FROM dbo.wages ORDER BY 'Total Salary'; GO 以下為結果集： Total Salary ------------ 20800.0000 41600.0000 62400.0000 83200.0000 10000.0000 20000.0000 30000.0000 40000.0000 45000.0000 50000.0000 120000.0000 56000.0000 (12 row(s) affected)