在sql中如何求mtd,ytd ?
有一如下题目， 不知如何解答， 请教各位.
根据表A和表B数据 用SQL 或 存储过程得到C表结果.
表A:
salesDate product amount
2010-01-05 A 100
2010-01-21 B 50
2010-02-05 A 80
2010-03-08 A 200
2010-03-15 B 120
表B：
period_time start_time end_time
201001 2010-01-01 2010-01-31
201002 2010-02-01 2010-02-28
201003 2010-03-01 2010-03-31
表C
period_time product mtd ytd
201001 A 100 100
201001 B 50 50
201002 A 80 180
201002 B 0 50
201003 A 200 380
201003 B 120 170
[解决办法]
create table a(salesDate varchar(10),product varchar(2),amount int) insert into a select '2010-01-05', 'A', 100 union select '2010-01-21', 'B', 50 union select '2010-02-05', 'A', 80 union select '2010-03-08', 'A' ,200 union select '2010-03-15', 'B', 120 create table b (period_time varchar(6), start_time varchar(10), end_time varchar(10)) insert into b select '201001', '2010-01-01', '2010-01-31' union select '201002', '2010-02-01', '2010-02-28' union select '201003', '2010-03-01', '2010-03-31'--1：补录表中的数据；select m.period_time , m.product , isnull(n.amount,0) mtd into #tmpfrom (select b.* , t.product from b , (select distinct a.product from a) t) mleft join a n on datediff(mm,m.period_time+'01',n.salesDate) = 0 and m.product = n.product--2;根据完整数据表 叠加结果select t1.* , ytd = ( select sum(t2.mtd) from #tmp as t2 where t2.period_time <= t1.period_time and t2.product = t1.product) from #tmp as t1
