SQL SERVER
警務人力調派─使用COALESCE()函數。以及COALESCE()函數與ISNULL()函數的比較
2018/12/04 15:41:31
0
2814
警務人力調派─使用COALESCE()函數。以及COALESCE()函數與ISNULL()函數的比較
| 簡介 |
此範例需求為警察勤務人力查詢與調派，人力的調派順序為：總部、分局、派出所。 當某勤務總部無人力可用時改為由分局調派，總部與分局皆無人力時改由派出所調派。 |
| 作者 |
鍾文彥 |
●原始碼與執行結果
●範例解說
1. 測試資料
英文代碼ABCDE代表勤務名稱，其後的數字代表該警察單位該勤務的可調派人力。
當有人力勤務需求時，最優先由總部調派，再者從分局，最後才從派出所。
2. 執行結果
上圖紅框處為所有警察單位的可配置人力狀況，藍框處為調派後的結果。
COALESCE()的效果就是COALESCE(p1,p2,p3,.......pn)中，輸出第一個非NULL的值，參數由左到右的順序就是它的優先順序。
以A勤務而言只有苓雅分局才有，所以當然是輸出50人。
比較特殊的是C、D這些勤務代碼，不只一個警察單位有人力，那就是以COALESCE()所設定的順序輸出。
以D勤務舉例，有三個單位(總部、三民二分局、新興分局前金派出所)都有人力，那以第一順位輸出的就是總部的70人。
●與ISNULL()函數的比較
1. 參數個數
最明顯差別就是可接受的參數個數。
ISNULL()只接受2個：ISNULL(p1,p2)
COALESCE()可以接受多個：COALESCE(p1,p2,p3,...........pn)
2. 程式可讀性
COALESCE()既然特別獨立出來，那就有它的便利性。
以下就以
COALESCE(p1,p2,p3)這段原始碼，來比較看看如何以其它函數或運算式實現同樣的效果。
[CASE 運算式]
CASE
WHEN (p1 IS NOT NULL) THEN p1
WHEN (p2 IS NOT NULL) THEN p2
WHEN (p3 IS NOT NULL) THEN p3
ELSE NULL
END
以上這段跟COALESCE()是完全一樣的，SQL SERVER的最佳化工具會將COALESCE()重構為相對應的CASE運算式。
總而言之我們可以這樣說：COALESCE()函數是CASE運算式的簡式。
[ISNULL()函數]
ISNULL(p1,ISNULL(p2,ISNULL(p3,NULL)))
以上這段效果也是相同，但是可讀性差很多。
3. 效能差異
一般來說ISNULL()的效能稍好。
如果是單純的欄位：
COALESCE(p1,p2)
ISNULL(p1,p2)
那其實看不出什麼差異。
不過如果參數是一段子查詢，那就存在一些差異了。
如以下範例：
COALESCE(SELECT TOP 1 A FROM TABLE,0)
ISNULL(SELECT TOP 1 A FROM TABLE,0)
前面有提過，
COALESCE()會重構成CASE運算式
如下：
CASE
WHEN (SELECT TOP 1 A FROM TABLE) IS NOT NULL
THEN (SELECT TOP 1 A FROM TABLE)
ELSE 0
END
可以很清楚看到，資料表TABLE會被SELECT兩次，而ISNULL()只會評估一次，
資料量越大，效能差異會更有感。
4. 輸出的資料型態
COALESCE()及ISNULL()輸出的資料型態也不同，
COALESCE()輸出的資料型態使用它「輸出最高優先序」的參數資料型態。
ISNULL()輸出的資料型態使用它的「第一個參數」的資料型態。
講起來很抽象，我們直接看以下範例：
由上圖我們可以很清楚分辨所輸出結果的資料形態上的差異。
ISNULL()輸出它第一個參數的VARCHAR(3)，
COALESCE()輸出它非NULL的最高順位參數VARCHAR(100)。
所以以這個範例而言ISNULL()在使用上要特別注意，可能會有資料截斷的問題。
預期我們想看到的結果應該是123456789，但是因為所使用的型態是@p1的VARCHAR(3)，
資料就會被截斷為只剩下123。
5. 遵照的標準
COALESCE()遵照ANSI標準，
ISNULL()只存在於TSQL。
所以當資料庫更換時，COALESCE()函數是不需要改寫的。
●結語
表面上來看COALESCE()與ISNULL()似乎差異不大，甚至效果上來看COALESCE()可說是ISNULL()的擴充。
但是所表現的內涵，不管是可讀性、效能表現等等，實際上都存在滿大的差別。
某些差異使COALESCE()很好用，而其它差異反而會使ISNULL()更受青睞。
其實還是老話一句，既然我們在撰寫上有多種選擇，還是要根據系統需求、團隊風格或其它種種的因素，來選擇對自己最有利的功能。
相關文章
最新文章
