,,

使用coalesce和nullif的組合來減輕寫sql的工作量

admin

2011年8月2日 17:11 本文熱度 3349

今天幫朋友調了一個網站，無意中翻了一個sp，看到了一段很長的select語句，這個select語句之所以長，是因為有好幾個一般復雜的case語句跟在select的后面。我們摘取其中的一個字段的邏輯規則和數據來做我們的測試數據：

create table tbl (id int, type_a int)

insert into tbl values (1000,1000)
insert into tbl values (999,999)
insert into tbl values (998,998)
insert into tbl values (997,997)
insert into tbl values (996,996)
insert into tbl values (995,null)
insert into tbl values (994,null)
insert into tbl values (993,null)
insert into tbl values (992,null)
insert into tbl values (991,null)

邏輯非常簡單：當type_a為997或null的時候，我們要讓輸出的type_a字段值為0。
OK，這個SQL語句當然有多種寫法，朋友的sql是這樣寫的：

select
    case
        when (type_a is null or type_a=997) then 0
        else type_a
    end as type_a
from tbl

如果需要控制的字段一多，那這個及時已經使用了縮進的select也看起來很復雜了，時間久了想改動這個sp的邏輯就有些吃力了，我們常常在做計劃時會說“半小時搞定這個問題”，但是往往在做的時候都會超過這個時間，原因就在于我們總有從一團亂麻中找到入手點。復雜的代碼和邏輯往往是解決問題中難啃的骨頭。那么有什么好辦法優化一下嗎？

select coalesce(nullif(type_a,997),0) as type_a from tbl

Well，上面寫了6行的sql就被這1行所替代了。

nullif接受兩個參數，如果兩個參數相等，那么返回null，否則返回第一個參數
coalesce接受N個參數，返回第一個不為null的參數

So，當您遇到處理一個如下所示的計算工資的問題的時候，不妨這樣來解決：

create table salary (e_id uniqueidentifier, byMonth int, byHalfYear int, byYear int)

insert into salary values (newid(),9000,null,null)
insert into salary values (newid(),null,60000,null)
insert into salary values (newid(),null,null,150000)

每個雇員有3種薪資計算方式（按月，按半年，按年）來發放工資，如果我們想統計每個員工的年薪，那這樣一句就夠了：

select e_id,coalesce(byMonth*12,byHalfYear*2,byYear) as salary_amount from salary

結果：

e_id salary_amount
------------------------------------ -------------
8935330D-2B73-4FEF-941A-768D7A8CCB6C 108000
52A3CE16-74FD-4D5D-BB4F-F5F67A1E9D2F 120000
06B6B924-EAB2-4187-B733-EBB56B62E793 150000

參考：
COALESCE (Transact-SQL)
NULLIF (Transact-SQL)

該文章在 2011/8/2 17:11:42 編輯過

關鍵字查詢

工作

正在查詢...

點晴ERP是一款針對中小制造業的專業生產管理軟件系統,系統成熟度和易用性得到了國內大量中小企業的青睞。

點晴PMS碼頭管理系統主要針對港口碼頭集裝箱與散貨日常運作、調度、堆場、車隊、財務費用、相關報表等業務管理，結合碼頭的業務特點，圍繞調度、堆場作業而開發的。集技術的先進性、管理的有效性于一體，是物流碼頭及其他港口類企業的高效ERP管理信息系統。

點晴WMS倉儲管理系統提供了貨物產品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質期管理,貨位管理,庫位管理,生產管理,WMS管理系統,標簽打印,條形碼,二維碼管理,批號管理軟件。

點晴免費OA是一款軟件和通用服務都免費，不限功能、不限時間、不限用戶的免費OA協同辦公管理系統。

日本xxxx18视频在线观看-日本xxxx1819-日本xxxwww在线观看-日本xxx-日本xx-日本www在线视频

使用coalesce和nullif的組合來減輕寫sql的工作量