无人问津的帖子又来了...
--> 测试数据：[获取表]
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 兑换表,获取表
[解决办法]
如果月份不同,改这个:
select distinct 卡号,2011 as 年份,8 as 月份 from 获取表
union
select distinct 卡号,2011,9 from 获取表
union
select distinct 卡号,2011,10 from 获取表
其中的年月改成你要年月.
[解决办法]
无有，但有神来问津就好。