mssql sqlserver sum显示多列的方法分享


摘要:
下文讲述sqlserver sql脚本中,同时对多列进行sum计算,如下所示:
实验环境:sql server 2008 R2


在sqlserver中,当我们需要sum显示多列时,此时我们可以采取同时对表的不同列执行sum操作,如下所示:

    create table [maomao365]
(keyId int identity,
qty1 int,
qty2 int,
qty3 int)
go

/*生成数据*/
insert into [maomao365]
(qty1,qty2,qty3)
values
(8,9,10),
(1,2,3),
(5,4,null),
(6,11,13),
(7,12,14)
go


/*多列分别sum求和*/

select sum(qty1) as [qty1],sum(qty2) as [qty2],
sum(qty3) as [qty3] from [maomao365]


/*sum同时多列一起求和*/
select sum(isnull(qty1,0)+isnull(qty2,0)+isnull(qty3,0)) as [qty] 
 from [maomao365]


select sum(qty1+qty2+qty3) as [qty] 
 from [maomao365]

go
truncate table [maomao365]


sum求和注意事项:
当使用sum聚合函数进行求和运算时,系统会过滤掉列值为null的行