34,593
社区成员
发帖
与我相关
我的任务
分享
create table A(id int identity(1,1) not null primary key,date varchar(50),shangping varchar(10),bianhua int)
insert A(date,shangping,bianhua)
select '20110101','A','1'
union all
select '20110101','B','12'
union all
select '20110101','C','9'
union all
select '20110102','A','3'
union all
select '20110102','B','1'
union all
select '20110103','A','4'
union all
select '20110103','C','5'
select date,shangping,case when date='20110101' then bianhua else
(select sum(bianhua) from A b2 where b1.shangping=b2.shangping and b2.date<=b1.date ) end from A b1
select *,stuff((select '+'+ltrim(每日变化数) from tb where 商品 = t.商品 and 日期 <= t.日期),1,1,'')
+ '=' + ltrim((select sum(每日变化数) from tb where 商品 = t.商品 and 日期 <= t.日期))
from tb t
declare @rq1 datetime ,@rq2 datetime,@rq3 datetime
set @rq1='20110101'
set @rq2='20110103'
set @rq3=@rq1
select * into #temp from a where 1=2
while (@rq3<=@rq2)
begin
insert #temp select @rq3,商品,sum(每日变化数)
from a where 日期 between @rq1 and @rq3 group by 商品
set @rq3=dateadd(dd,1,@rq3)
end
select * from #temp
drop table #temp
select
日期, 商品,(select sum(每日变化数) from tb where 商品 = t.商品 and 日期 <= t.日期)) as 汇总变化数
from
tb t