mssql sqlserver sum函数计算后的结果中包含null的处理方法分享


摘要:
在mssql进行聚合计算时,有时候查询出来的结果包含null,此时我们可采用以下方法将null转换为0



方法1:使用isnull替换

    

 create table #maomao365 
(qty int)
go


insert into #maomao365(qty)
values(1),(2),(3),(null),(5),(6),
(null)
go


select isnull(sum(qty),0) from #maomao365
where qty is null 




go
truncate table #maomao365 
drop table #maomao365


方法2:使用case when 替换

    

 create table #maomao365 
(qty int)
go


insert into #maomao365(qty)
values(1),(2),(3),(null),(5),(6),
(null)
go


select case 
         when sum(qty) is null then 0
          else   sum(qty) 
          end 
           from #maomao365
          where qty is null 




go
truncate table #maomao365 
drop table #maomao365


方法3:用coalesce替换相应的值

    

 create table #maomao365 
(qty int)
go


insert into #maomao365(qty)
values(1),(2),(3),(null),(5),(6),
(null)
go


select coalesce(sum(qty),0) from #maomao365
where qty is null 




go
truncate table #maomao365 
drop table #maomao365