日期:2014-05-18 浏览次数:20627 次
stock表,结构与记录如下: 日期(rdate) 数量(qty) 进出(dc) 4/1 40 1 4/1 50 1 4/5 20 -1 4/15 10 1 4/20 20 -1 希望生成这样的表 日期 余额 4/1 90 4/2 90 4/3 90 4/4 90 4/5 70 4/6 70 ... 4/14 70 4/15 80 4/16 80 ... 4/20 60 ... create table stock (rdate char(10), qty int, dc int) insert into stock values ('2012-04-01',40,1) insert into stock values ('2012-04-01',50,1) insert into stock values ('2012-04-05',20,-1) insert into stock values ('2012-04-15',10,1) insert into stock values ('2012-04-20',20,-1) select date as rdate,qty=(select sum(qty*dc) from stock where rdate<=a.date) from (select convert(char(10),dateadd(day,number,'2012-04-01'),23) as date from master.dbo.spt_values where type='P' and number<=30) a ----结果 rdate qty 2012-04-01 90 2012-04-02 90 2012-04-03 90 2012-04-04 90 2012-04-05 70 2012-04-06 70 2012-04-07 70 2012-04-08 70 2012-04-09 70 2012-04-10 70 2012-04-11 70 2012-04-12 70 2012-04-13 70 2012-04-14 70 2012-04-15 80 2012-04-16 80 2012-04-17 80 2012-04-18 80 2012-04-19 80 2012-04-20 60 2012-04-21 60 2012-04-22 60 2012-04-23 60 2012-04-24 60 2012-04-25 60 2012-04-26 60 2012-04-27 60 2012-04-28 60 2012-04-29 60 2012-04-30 60 2012-05-01 60