数据库sum函数的问题

学勇 2013-10-10 10:49:15

现在有图1的表

，
对diff1列求两种和，加在表后面，得到图2的样子

，
求数据库语句怎么写

...全文

266 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

ai_li7758521 2013-10-10

打赏
举报

;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

Landa_Peter 2013-10-10

打赏
举报

楼上两个都可以

Andy__Huang 2013-10-10

打赏
举报


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

Landa_Jimmy 2013-10-10

打赏
举报


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

学勇 2013-10-10

打赏
举报

引用 1 楼 u012158899 的回复:

你用文本形式贴出来看看，这样不好弄测试数据

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

Landa_Peter 2013-10-10

打赏
举报

你用文本形式贴出来看看，这样不好弄测试数据

学勇 2013-10-10

打赏
举报

引用 6 楼 ai_li7758521 的回复:

;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

这个写法更好，省代码量，赞一个（结贴后才看到这个答案，真不好意思）

hunkwangshz 2013-10-10

打赏
举报

SQL2012 with 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