SQL Server COALESCE()函數的創新應用

COALESCE()函數可以接受一系列的值,如果列表中所有項都爲空(null),那麽只使用一個值。然後,它將返回第一個非空值。這一技巧描述了創造性使用SQL Server 中COALESCE()函數的兩種方法。

這裏有一個簡單的例子:有一個Persons數據表,它有三個字段FirstName、MiddleName和LastName。表中包含以下值:

John A. MacDonald

Franklin D. Roosevelt

Madonna

Cher

Mary Weilage

如果你想用一個字符串列出他們的全名,下面給出了如何使用COALESCE()函數完成此功能:

SELECT FirstName + '' '' +COALESCE(MiddleName,'''')+ '' '' +COALESCE(LastName,'''')

如果你不想每個查詢都這樣寫,列表A顯示了如何將它轉換成一個函數。這樣當你需要使用這個腳本的時候(不管每個列的實際值是什麽),可以直接調用該函數並傳遞三個字段參數。在下面的例子中,我傳遞給函數的參數是人名,但是你可以用字段名替代得到同樣的結果:

SELECT dbo.WholeName(''James'',NULL,''Bond'')

UNION

SELECT dbo.WholeName(''Cher'',NULL,NULL)

UNION

SELECT dbo.WholeName(''John'',''F.'',''Kennedy'')

測試結果如下:

James Bond

Cher

John F. Kennedy

你可能會注意到我們的一個問題,在James Bond這個名字中有兩個空格。通過修改@result這一行可以改正這個問題,如下所示:

SELECT @Result = LTRIM(@first + '' '' + COALESCE(@middle,'''') + '' '') + COALESCE(@last,'''')

下面是COALESCE()函數的另一個應用。在本例中,我們將顯示一個支付給員工的工資單。問題是對于不同的員工工資標准是不同的(例如,有些員工是按小時支付,按工作量每周發一次工資或是按責任支付)。列表B中是創建一個樣表的代碼。下面是一些示例記錄,每個是一種類型:

1 18.00 40 NULL NULL NULL NULL

2 NULL NULL 4.00 400 NULL NULL

3 NULL NULL NULL NULL 800.00 NULL

4 NULL NULL NULL NULL 500.00 600

用下面的代碼在同一列中列出支付給員工的總額(不管它們的支付標准):

SELECT

EmployeeID,

COALESCE(HourlyWage * HoursPerWeek,0)+

COALESCE(AmountPerPiece * PiecesThisWeek,0)+

COALESCE(WeeklySalary + CommissionThisWeek,0)AS Payment

FROM [Coalesce_Demo].[PayDay]

結果如下:

EmployeeID Payment

1 720.00

2 1600.00

3 800.00

4 1100.00

你可能需要在應用程序中多處使用這一計算方法,雖然這種表示可以完成任務,但是看起來不是很美觀。下面列出了如何使用一個單獨的求和列來完成這項工作:

ALTERTABLE Coalesce_Demo.PayDay

ADD Payment AS

COALESCE(HourlyWage * HoursPerWeek,0)+

COALESCE(AmountPerPiece * PiecesThisWeek,0)+

COALESCE(WeeklySalary + CommissionThisWeek,0)

這樣只要使用SELECT *就可以顯示預先計算好的結果。

小結

本文介紹了使用COALESCE()函數一些特殊場合和特殊方式。就我的經驗看來,COALESCE()函數最常出現在一個具體的內容中,如一個查詢或視圖或存儲過程中。

你可以將COALESCE()放在一個函數中來使用它,也可以通過將它放在一個單獨的計算列中優化性能,並總能獲得結果。

· 把年齡相仿的獅虎熊放一起,誰更厲害?結果出人意料

很多人都想知道獅子、老虎和熊打起來誰最厲害,于是便有好事之人把這三種動物關在一起...

· 湖北宜昌三峽壩區水面驚現神秘動物

近日,湖北宜昌,一段視頻在當地熱傳:有網友在三峽壩區拍到神秘動物,體型碩大數米長...

· 什麽是語段?語段的類型以及和句群、段落的區別與聯系是什麽?

句群是最高級的語言單位。 段落(自然段)是章法單位...

 
SQL Server和Oracle的常用函數對比
  數學函數  1.絕對值  S:select abs(-1) value  O:select abs(-1) value from dual    2.取整(大)  S:select ceiling(-1.001) value  O:select ceil(-1.001) value from dual   ...查看完整版>>SQL Server和Oracle的常用函數對比
 
sql server日期時間函數
Sql Server中的日期與時間函數 1. 當前系統日期、時間 select getdate() 2. dateadd 在向指定日期加上一段時間的基礎上,返回新的 datetime 值 例如:向日期加上2天 select dateadd(day,2,'2004-10-15')...查看完整版>>sql server日期時間函數
 
用 C# 開發 SQL Server 2005 的自定義聚合函數
在 SQL 中,經常需要對數據按組進行自定義的聚合操作,比如用逗號連接一系列表示 ID 的數字,但默認只有 SUM, MAX, MIN, AVG 等聚合函數。在 SQL Server 2005 中提供了編寫 CLR 的托管代碼的支持,我們可以用來寫自定...查看完整版>>用 C# 開發 SQL Server 2005 的自定義聚合函數
 
SQL Server 2005 - 善用 OPENROWSET 函數來存取大型對象(LOB)
我們在「Visual Basic 2005 檔案 IO 與資料存取秘訣」一書的第七章,詳細探討了如何于前端程序處理大型對象(LOB)。有讀者詢問,SQL Server 2005 本身是否提供任何的 Transact-SQL 陳述式來處理 LOB 呢?答案當然是...查看完整版>>SQL Server 2005 - 善用 OPENROWSET 函數來存取大型對象(LOB)
 
Sql server如何創建語言輔助函數
在現在這樣一個全球化環境中,因爲在不同的語言中有很多不同的語法規則,所以以前很多簡單的任務現在都變得很困難。你可以將一門特定的語言分成一組語法規則和針對這些規則的異常(以及一個基本詞語),從而將這些任...查看完整版>>Sql server如何創建語言輔助函數