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

admin

2011年8月2日 17:11 本文热度 2784

今天帮朋友调了一个网站，无意中翻了一个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协同办公管理系统。