使用coalesce和nullif的组合来减轻写sql的工作量

今天帮朋友调了一个网站，无意中翻了一个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)

附:
SQL Server 2005中的except/intersect和outer apply