mysql中sum函数结果是null_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