数据是这样的:
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
请高手指教,立马给分!
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
请高手指教,立马给分!
先小记,然后在合计,合计了在总计,。我勒个去,烦死了。而且我只能用greadview显示数据。
关注中
--> 测试数据:[tbl]
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(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
)
select name,[date],num,total from m/*
name date num total
a 1-1号 1 1
b 1-2号 4 5
a 1-3号 8 13
a 1-4号 5 18
b 1-5号 6 24
b 1-6号 9 33
*/起个早,写了一个
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来分别统计的,修改了一下
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 行受影响)
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 行受影响)