create table [获取表]([卡号] int,[获取值] int,[年份] int,[月份] int)
insert [获取表]
select 1102,460,2010,11 union all
select 1102,230,2010,11 union all
select 1102,530,2010,11 union all
select 1102,265,2010,11 union all
select 1102,530,2010,11 union all
select 1102,230,2010,12 union all
select 1102,265,2010,12 union all
select 1102,530,2010,12 union all
select 1102,530,2010,12 union all
select 1102,230,2011,1 union all
select 1102,230,2011,1 union all
select 1102,460,2011,1 union all
select 1102,690,2011,1 union all
select 1102,1150,2011,1 union all
select 1102,1700,2011,1 union all
select 1102,265,2011,1 union all
select 1102,1325,2011,1 union all
select 1102,1325,2011,1 union all
select 1102,1060,2011,2 union all
select 1102,530,2011,3 union all
select 1102,530,2011,3 union all
select 1102,530,2011,4 union all
select 1102,795,2011,4 union all
select 1102,795,2011,4 union all
select 1102,530,2011,4 union all
select 1102,530,2011,5 union all
select 1102,530,2011,5 union all
select 1102,133,2011,5 union all
select 1102,266,2011,5 union all
select 1102,340,2011,9 union all
select 1102,340,2011,10 union all
select 1102,340,2011,10 union all
select 1102,680,2011,11
create table [兑换表]([卡号] int,[兑换值] int,[年份] int,[月份] int)
insert [兑换表]
select 1102,1920,2010,12 union all
select 1102,1512,2011,1 union all
select 1102,8550,2011,3 union all
select 1102,975,2011,4 union all
select 1102,3870,2011,9 union all
select 1102,430,2011,10 union all
select 1102,916,2011,11 union all
select 1102,550,2011,12
go
select distinct 卡号,
(select sum(获取值) from 获取表 where 卡号=a.卡号 and (年份<a.年份 or (年份=a.年份 and 月份<=a.月份))) 总获取,
(select sum(兑换值) from 兑换表 where 卡号=a.卡号 and (年份<a.年份 or (年份=a.年份 and 月份<=a.月份))) 总兑换,
年份,月份 FROM (
select distinct 卡号,2011