您当前的位置: 有一如下题目， 不知如何解答， 请教各位.
根据表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
------解决方案--------------------
- SQL code
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 #tmp
from
(select b.* , t.product from b , (select distinct a.product from a) t) m
left 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