数据库sum函数的问题
现在有图1的表
，
对diff1列求两种和，加在表后面，得到图2的样子
，
求数据库语句怎么写 数据库 sum 求和 增加列
[解决办法]
create table #tab(BillNo varchar(50),diff1 int)
insert into #tab
select 20131009001,0 union all
select 20131009001,0 union all
select 20131009001,27 union all
select 20131009001,27 union all
select 20131009001,0 union all
select 20131009002,0 union all
select 20131009002,0 union all
select 20131009002,0 union all
select 20131009003,0 union all
select 20131009003,0 union all
select 20131009003,0 union all
select 20131009003,0 union all
select 20131009003,0
select *,
(select SUM(diff1) from #tab b where a.BillNo=b.BillNo
group by BillNo)sum,
(select SUM(diff1) from #tab)sum1
from #tab a
-------------------------------------------
BillNo diff1 sum sum1
-------------------------------------------------- ----------- ----------- -----------
20131009001 0 54 54
20131009001 0 54 54
20131009001 27 54 54
20131009001 27 54 54
20131009001 0 54 54
20131009002 0 0 54
20131009002 0 0 54
20131009002 0 0 54
20131009003 0 0 54
20131009003 0 0 54
20131009003 0 0 54
20131009003 0 0 54
20131009003 0 0 54
;with cte(BillNo,diff1) as
(
select 20131009001,0
union all select 20131009001,0
union all select 20131009001,27
union all select 20131009001,27
union all select 20131009001,0
union all select 20131009002,0
union all select 20131009002,0
union all select 20131009002,0
union all select 20131009003,0
union all select 20131009003,0
union all select 20131009003,0
union all select 20131009003,0
union all select 20131009003,0
)
select *,[sum]=(select SUM(diff1) from cte b where a.BillNo=b.BillNo)
,[sum1]=(select SUM(diff1) from cte)
from cte a
/*
BillNodiff1sumsum1
2013100900105454
2013100900105454
20131009001275454
20131009001275454
2013100900105454
201310090020054
201310090020054
201310090020054
201310090030054
201310090030054
201310090030054
201310090030054
201310090030054
*/
