数据库sum函数的问题

Question

现在有图1的表
，
对diff1列求两种和，加在表后面，得到图2的样子
，
求数据库语句怎么写数据库sum求和增加列

Answer 1

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

Answer 2

;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/*
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
*/

Answer 3

;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
)
--SQL 2005+
SELECT *,[sum]=SUM(diff1) OVER(PARTITION BY BillNo),sum1=SUM(diff1) OVER()
FROM cte

Answer 4

SQL2012with t as
(
select 1 as col,timestr,val
from myTable2
)
SELECT timestr,val,
SUM(val)  OVER(PARTITION BY timestr
ORDER BY timestr) AS [sum],
sum(val) over(partition by col
order by timestr) as sum1
FROM t