Coalesce (MS SQL Server)——取指定内容（列）中第一个不为空的值

来源：互联网发布：淘宝高佣金手机查找编辑：程序博客网时间：2024/05/16 11:31

Coalesce 获得参数中第一个不为空的表达式。

语法：
COALESCE ( expression [ ,...n ] )

例子：
CREATE TABLE wages                /*创建表wages*/
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)             /*插入值*/
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,                    /*取hourly_wage*40*52和salary列中不为空的列的值*/
   salary,
   commission * num_sales) AS money) AS 'Total Salary'
FROM wages
GO

Here is the result set:

Total Salary ------------ 20800.000041600.000062400.000083200.000010000.000020000.000030000.000040000.000045000.000050000.0000120000.000056000.0000(12 row(s) affected)