使用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