Transact—SQL中的對(duì)空值的處理函數(shù)COALESCE

桂云秋+張磊+周揚(yáng)+朱臣

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

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

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

1語法及說明

COALESCE （ expression [ ，...n ] ）

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

2應(yīng)用實(shí)例

在以下示例中，wages 表中包括以下三列有關(guān)雇員的年薪的信息：hourly wage、salary 和 commission。但是，每個(gè)雇員只能接受一種付款方式。若要確定支付給所有雇員的金額總數(shù)，請(qǐng)使用 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 的表達(dá)式將視為 NOT NULL，而包含具有非空參數(shù)的 COALESCE 的表達(dá)式將視為 NULL。在 SQL Server 中，若要對(duì)包含具有非空參數(shù)的 COALESCE 的表達(dá)式創(chuàng)建索引，可以使用 PERSISTED 列屬性將計(jì)算列持久化，如以下語句所示：

CREATE TABLE #CheckSumTest

（

ID int identity ，

Num int DEFAULT （ RAND（） * 100 ） ，

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

）

參考文獻(xiàn)

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

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