桂云秋+張磊+周揚+朱臣

摘 要 在數(shù)據(jù)庫查詢中，經(jīng)常會遇到對空值的處理，如何處理不當可能會帶來意想不到的結(jié)果。COALESCE函數(shù)能返回其參數(shù)中第一個非空表達式，在處理空值時常有非常好的效果。

關(guān)鍵詞 Transact-SQL COALESCE函數(shù) 應(yīng)用

在數(shù)據(jù)庫查詢中，經(jīng)常會遇到對空值的處理，如何處理不當可能會帶來意想不到的結(jié)果。COALESCE函數(shù)能返回其參數(shù)中第一個非空表達式，在處理空值時常有非常好的效果。

1語法及說明

COALESCE （ expression [ ，...n ] ）

其中，expression 任何類型的表達式。返回數(shù)據(jù)類型優(yōu)先級最高的 expression 的數(shù)據(jù)類型。如果所有參數(shù)均為 NULL，則 COALESCE 返回 NULL。但是，至少應(yīng)有一個空值為 NULL 類型。

2應(yīng)用實例

在以下示例中，wages 表中包括以下三列有關(guān)雇員的年薪的信息：hourly wage、salary 和 commission。但是，每個雇員只能接受一種付款方式。若要確定支付給所有雇員的金額總數(shù)，請使用 COALESCE 函數(shù)，它只接受在 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 函數(shù)等效：

CASE

WHEN （expression1 IS NOT NULL） THEN expression1

...

WHEN （expressionN IS NOT NULL） THEN expressionN

ELSE NULL

END

3應(yīng)用說明

盡管 ISNULL 等效于 COALESCE，但它們的行為是不同的。包含具有非空參數(shù)的 ISNULL 的表達式將視為 NOT NULL，而包含具有非空參數(shù)的 COALESCE 的表達式將視為 NULL。在 SQL Server 中，若要對包含具有非空參數(shù)的 COALESCE 的表達式創(chuàng)建索引，可以使用 PERSISTED 列屬性將計算列持久化，如以下語句所示：

CREATE TABLE #CheckSumTest

（

ID int identity ，

Num int DEFAULT （ RAND（） * 100 ） ，

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

）

參考文獻

[1] 杜佰林.網(wǎng)絡(luò)數(shù)據(jù)庫SQL Server 2000[M].清華出版社，2009，9.

[2] 何薇，舒后.網(wǎng)絡(luò)數(shù)據(jù)庫技術(shù)與應(yīng)用[M].清華大學出版社，2014，10.