桂云秋+张磊+周扬+朱臣

摘 要 在数据库查询中，经常会遇到对空值的处理，如何处理不当可能会带来意想不到的结果。COALESCE函数能返回其参数中第一个非空表达式，在处理空值时常有非常好的效果。

关键词 Transact-SQL COALESCE函数 应用

在数据库查询中，经常会遇到对空值的处理，如何处理不当可能会带来意想不到的结果。COALESCE函数能返回其参数中第一个非空表达式，在处理空值时常有非常好的效果。

1语法及说明

COALESCE （ expression [ ，...n ] ）

其中，expression 任何类型的表达式。返回数据类型优先级最高的 expression 的数据类型。如果所有参数均为 NULL，则 COALESCE 返回 NULL。但是，至少应有一个空值为 NULL 类型。

2应用实例

在以下示例中，wages 表中包括以下三列有关雇员的年薪的信息：hourly wage、salary 和 commission。但是，每个雇员只能接受一种付款方式。若要确定支付给所有雇员的金额总数，请使用 COALESCE 函数，它只接受在 hourly_wage、salary 和 commission 中找到的非空值。

SET NOCOUNT ON；

USE master；

IF EXISTS （SELECT name FROM sys.tables

WHERE name = wages）

DROP TABLE wages；

CREATE TABLE wages

（

emp_id tinyint identity，

hourly_wage decimal NULL，

salary decimal NULL，

commission decimal NULL，

num_sales tinyint NULL

）；

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）；

SET NOCOUNT OFF；

SELECT CAST（COALESCE（hourly_wage * 40 * 52，

salary，

commission * num_sales） AS money） AS Total Salary

FROM wages；

COALESCE（expression1，...n） 与此 CASE 函数等效：

CASE

WHEN （expression1 IS NOT NULL） THEN expression1

...

WHEN （expressionN IS NOT NULL） THEN expressionN

ELSE NULL

END

3应用说明

尽管 ISNULL 等效于 COALESCE，但它们的行为是不同的。包含具有非空参数的 ISNULL 的表达式将视为 NOT NULL，而包含具有非空参数的 COALESCE 的表达式将视为 NULL。在 SQL Server 中，若要对包含具有非空参数的 COALESCE 的表达式创建索引，可以使用 PERSISTED 列属性将计算列持久化，如以下语句所示：

CREATE TABLE #CheckSumTest

（

ID int identity ，

Num int DEFAULT （ RAND（） * 100 ） ，

RowCheckSum AS COALESCE（ CHECKSUM（ id ， num ） ， 0 ） PERSISTED PRIMARY KEY

）

参考文献

[1] 杜佰林.网络数据库SQL Server 2000[M].清华出版社，2009，9.

[2] 何薇，舒后.网络数据库技术与应用[M].清华大学出版社，2014，10.