--> 测试数据：[获取表]
if object_id('[获取表]') is not null drop table [获取表]
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
--> 测试数据：[兑换表]
if object_id('[兑换表]') is not null drop table [兑换表]
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
******
查询结果
卡号 总获取 总兑换 剩余 年份 月份
1102 17174 12957 4217 2011 8
1102 17514 12957 4557 2011 9
1102 18194 16827 1367 2011 10
------解决方案--------------------
你怎么知道这帖子无人问津的?
------解决方案--------------------
- SQL code
create table [获取表]([卡号] int,[获取值] int,[年份] int,[月份] int)insert [获取表]select 1102,460,2010,11 union allselect 1102,230,2010,11 union allselect 1102,530,2010,11 union allselect 1102,265,2010,11 union allselect 1102,530,2010,11 union allselect 1102,230,2010,12 union allselect 1102,265,2010,12 union allselect 1102,530,2010,12 union allselect 1102,530,2010,12 union allselect 1102,230,2011,1 union allselect 1102,230,2011,1 union allselect 1102,460,2011,1 union allselect 1102,690,2011,1 union allselect 1102,1150,2011,1 union allselect 1102,1700,2011,1 union allselect 1102,265,2011,1 union allselect 1102,1325,2011,1 union allselect 1102,1325,2011,1 union allselect 1102,1060,2011,2 union allselect 1102,530,2011,3 union allselect 1102,530,2011,3 union allselect 1102,530,2011,4 union allselect 1102,795,2011,4 union allselect 1102,795,2011,4 union allselect 1102,530,2011,4 union allselect 1102,530,2011,5 union allselect 1102,530,2011,5 union allselect 1102,133,2011,5 union allselect 1102,266,2011,5 union allselect 1102,340,2011,9 union allselect 1102,340,2011,10 union allselect 1102,340,2011,10 union allselect 1102,680,2011,11create table [兑换表]([卡号] int,[兑换值] int,[年份] int,[月份] int)insert [兑换表]select 1102,1920,2010,12 union allselect 1102,1512,2011,1 union allselect 1102,8550,2011,3 union allselect 1102,975,2011,4 union allselect 1102,3870,2011,9 union allselect 1102,430,2011,10 union allselect 1102,916,2011,11 union allselect 1102,550,2011,12goselect 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 as 年份,8 as 月份 from 获取表unionselect distinct 卡号,2011,9 from 获取表unionselect distinct 卡号,2011,10 from 获取表)a/*卡号 总获取 总兑换 年份 月份----------- ----------- ----------- ----------- -----------1102 17174 12957 2011 81102 17514 16827 2011 91102 18194 17257 2011 10(3 行受影响)*/godrop table 兑换表,获取表