求教关于时段汇总的问题

Question

--自己加个临时表,也可用select生成.
create table tb1(日期 datetime,原材料 int,在产品 int,成品 int,总库存 int) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-01',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-02',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-03',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-04',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-05',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-15',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-16',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-25',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-04-01',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-04-26',400,200,300) 
create table tb2(日期 datetime)
insert into tb2 values('2008-01-01')
insert into tb2 values('2008-01-15')
insert into tb2 values('2008-02-01')
insert into tb2 values('2008-02-15')
insert into tb2 values('2008-03-01')
insert into tb2 values('2008-03-15')
insert into tb2 values('2008-04-01')
insert into tb2 values('2008-04-15')
insert into tb2 values('2008-05-01')
insert into tb2 values('2008-05-15')
insert into tb2 values('2008-06-01')
insert into tb2 values('2008-06-15')
insert into tb2 values('2008-07-01')
insert into tb2 values('2008-07-15')
insert into tb2 values('2008-08-01')
insert into tb2 values('2008-08-15')
insert into tb2 values('2008-09-01')
insert into tb2 values('2008-09-15')
insert into tb2 values('2008-10-01')
insert into tb2 values('2008-10-15')
insert into tb2 values('2008-11-01')
insert into tb2 values('2008-11-15')
insert into tb2 values('2008-12-01')
insert into tb2 values('2008-12-15')goselect 日期 , 总库存 = isnull((select sum(原材料+在产品+成品) from tb1 where 日期 <= tb2.日期),0) from tb2drop table tb1,tb2/*
日期                                                     总库存         
------------------------------------------------------ ----------- 
2008-01-01 00:00:00.000                                0
2008-01-15 00:00:00.000                                0
2008-02-01 00:00:00.000                                0
2008-02-15 00:00:00.000                                0
2008-03-01 00:00:00.000                                900
2008-03-15 00:00:00.000                                5400
2008-04-01 00:00:00.000                                8100
2008-04-15 00:00:00.000                                8100
2008-05-01 00:00:00.000                                9000
2008-05-15 00:00:00.000                                9000
2008-06-01 00:00:00.000                                9000
2008-06-15 00:00:00.000                                9000
2008-07-01 00:00:00.000                                9000
2008-07-15 00:00:00.000                                9000
2008-08-01 00:00:00.000                                9000
2008-08-15 00:00:00.000                                9000
2008-09-01 00:00:00.000                                9000
2008-09-15 00:00:00.000                                9000
2008-10-01 00:00:00.000                                9000
2008-10-15 00:00:00.000                                9000
2008-11-01 00:00:00.000                                9000
2008-11-15 00:00:00.000                                9000
2008-12-01 00:00:00.000                                9000
2008-12-15 00:00:00.000                                9000（所影响的行数为 24 行）
*/

Answer 1

--通过select建立一个子表.
create table tb1(日期 datetime,原材料 int,在产品 int,成品 int,总库存 int) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-01',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-02',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-03',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-04',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-05',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-15',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-16',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-03-25',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-04-01',400,200,300) 
insert into tb1(日期,原材料,在产品,成品)values('2008-04-26',400,200,300) goselect 日期 , 总库存 = isnull((select sum(原材料+在产品+成品) from tb1 where 日期 <= tb2.日期),0) from 
(
select '2008-01-01' 日期 union select '2008-01-15' union select '2008-02-01' union select '2008-02-15' 
union select '2008-03-01' union select '2008-03-15' union select '2008-04-01' union select '2008-04-15' 
union select '2008-05-01' union select '2008-05-15' union select '2008-06-01' union select '2008-06-15' 
union select '2008-07-01' union select '2008-07-15' union select '2008-08-01' union select '2008-08-15' 
union select '2008-09-01' union select '2008-09-15' union select '2008-10-01' union select '2008-10-15' 
union select '2008-11-01' union select '2008-11-15' union select '2008-12-01' union select '2008-12-15' 
) tb2drop table tb1/*
日期         总库存         
---------- ----------- 
2008-01-01 0
2008-01-15 0
2008-02-01 0
2008-02-15 0
2008-03-01 900
2008-03-15 5400
2008-04-01 8100
2008-04-15 8100
2008-05-01 9000
2008-05-15 9000
2008-06-01 9000
2008-06-15 9000
2008-07-01 9000
2008-07-15 9000
2008-08-01 9000
2008-08-15 9000
2008-09-01 9000
2008-09-15 9000
2008-10-01 9000
2008-10-15 9000
2008-11-01 9000
2008-11-15 9000
2008-12-01 9000
2008-12-15 9000（所影响的行数为 24 行）*/