&

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

admin

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

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

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)

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

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

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

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

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

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

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

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種薪資計算方式（按月，按半年，按年）來發(fā)放工資，如果我們想統(tǒng)計每個員工的年薪，那這樣一句就夠了：

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

結(jié)果：

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 編輯過

關(guān)鍵字查詢

工作

相關(guān)文章

正在查詢...

點晴ERP是一款針對中小制造業(yè)的專業(yè)生產(chǎn)管理軟件系統(tǒng),系統(tǒng)成熟度和易用性得到了國內(nèi)大量中小企業(yè)的青睞。

點晴PMS碼頭管理系統(tǒng)主要針對港口碼頭集裝箱與散貨日常運作、調(diào)度、堆場、車隊、財務(wù)費用、相關(guān)報表等業(yè)務(wù)管理，結(jié)合碼頭的業(yè)務(wù)特點，圍繞調(diào)度、堆場作業(yè)而開發(fā)的。集技術(shù)的先進(jìn)性、管理的有效性于一體，是物流碼頭及其他港口類企業(yè)的高效ERP管理信息系統(tǒng)。

點晴WMS倉儲管理系統(tǒng)提供了貨物產(chǎn)品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質(zhì)期管理,貨位管理,庫位管理,生產(chǎn)管理,WMS管理系統(tǒng),標(biāo)簽打印,條形碼,二維碼管理,批號管理軟件。

點晴免費OA是一款軟件和通用服務(wù)都免費，不限功能、不限時間、不限用戶的免費OA協(xié)同辦公管理系統(tǒng)。