日期:2014-05-18  浏览次数:20547 次

求助:在某一列上,求每一行之前该组的累计和
数据是这样的:
name,date,num
-----------------------
a,1-1号,1
b,1-2号,4
a,1-3号,8
a,1-4号,5
b,1-5号,6
b,1-6号,9

想统计出这样的结果
name,date,num,total
---------------------
a,1-1号,1,1
a,1-3号,8,9
a,1-4号,8,17(解释:a这一组当前行之前有两行,在该行统计时就是前两行num列值+本行num列值)
b,1-2号,4,4
b,1-5号,6,10
b,1-6号,9,19


请高手指教,立马给分!



------解决方案--------------------
SQL code

go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9

;with t
as(
select ROW_NUMBER()over(partition by name
order by getdate()) as id,
*,num  as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner join m b on a.id=b.id+1 and a.name=b.name
)
select name,[date],num,total from m order by name

/*
name    date    num    total
a    1-3号    8    8
a    1-4号    5    13
a    1-1号    1    14
b    1-2号    4    4
b    1-5号    6    10
b    1-6号    9    19
*/

刚刚看错了你是要按照name来分别统计的,修改了一下

------解决方案--------------------
SQL code
 
declare @tb table  (
[name] varchar(1),
[date] varchar(5),
[num] int
)
 
insert into  @tb values('a','1-1号',1),('b','1-2号',4),('a','1-3号',8),( 'a','1-4号',8 ),('b','1-5号',6),('b','1-6号',9)
select name,num,date,total from (
 select *,
 (
 select SUM([num]) from ((select *,rn=ROW_NUMBER()over(partition by name order by name,date ) from @tb)
 )m 
 where s.name=m.name and s.date>=m.date) as total
from 
(select *,rn=ROW_NUMBER()over(partition by name order by name,date ) from @tb)s 
  )e order by name,date
 



name num         date  total
---- ----------- ----- -----------
a    1           1-1号  1
a    8           1-3号  9
a    8           1-4号  17
b    4           1-2号  4
b    6           1-5号  10
b    9           1-6号  19

(6 行受影响)

------解决方案--------------------
SQL code
 
declare @tb table  (
[name] varchar(1),
[date] varchar(5),
[num] int
)
 
insert into  @tb values('a','1-1号',1),('b','1-2号',4),('a','1-3号',8),( 'a','1-4号',8 ),('b','1-5号',6),('b','1-6号',9)
select name,num,date,total from (
 select *,
 (
 select SUM([num]) from ((select *  from @tb)
 )m 
 where s.name=m.name and s.date>=m.date) as total
from 
(select *  from @tb)s 
  )e order by name,date
 
name num         date  total
---- ----------- ----- -----------
a    1           1-1号  1
a    8           1-3号  9
a    8           1-4号  17
b    4           1-2号  4
b    6           1-5号  10
b    9           1-6号  19

(6 行受影响)