获取表
卡号 获取值 年份 月份
1102 460 2010 11
1102 230 2010 11
1102 530 2010 11
1102 265 2010 11
1102 530 2010 11
1102 230 2010 12
1102 265 2010 12
1102 530 2010 12
1102 530 2010 12
1102 230 2011 1
1102 230 2011 1
1102 460 2011 1
1102 690 2011 1
1102 1150 2011 1
1102 1700 2011 1
1102 265 2011 1
1102 1325 2011 1
1102 1325 2011 1
1102 1060 2011 2
1102 530 2011 3
1102 530 2011 3
1102 530 2011 4
1102 795 2011 4
1102 795 2011 4
1102 530 2011 4
1102 530 2011 5
1102 530 2011 5
1102 133 2011 5
1102 266 2011 5
1102 340 2011 9
1102 340 2011 10
1102 340 2011 10
1102 680 2011 11
兑换表
卡号 兑换值 年份 月份
1102 1920 2010 12
1102 1512 2011 1
1102 8550 2011 3
1102 975 2011 4
1102 3870 2011 9
1102 430 2011 10
1102 916 2011 11
1102 550 2011 12查询结果
卡号 总获取 总兑换 剩余 年份 月份
1102 17174 12957 4217 2011 8
1102 17514 12957 4557 2011 9
1102 18194 16827 1367 2011 10
卡号 获取值 年份 月份
1102 460 2010 11
1102 230 2010 11
1102 530 2010 11
1102 265 2010 11
1102 530 2010 11
1102 230 2010 12
1102 265 2010 12
1102 530 2010 12
1102 530 2010 12
1102 230 2011 1
1102 230 2011 1
1102 460 2011 1
1102 690 2011 1
1102 1150 2011 1
1102 1700 2011 1
1102 265 2011 1
1102 1325 2011 1
1102 1325 2011 1
1102 1060 2011 2
1102 530 2011 3
1102 530 2011 3
1102 530 2011 4
1102 795 2011 4
1102 795 2011 4
1102 530 2011 4
1102 530 2011 5
1102 530 2011 5
1102 133 2011 5
1102 266 2011 5
1102 340 2011 9
1102 340 2011 10
1102 340 2011 10
1102 680 2011 11
兑换表
卡号 兑换值 年份 月份
1102 1920 2010 12
1102 1512 2011 1
1102 8550 2011 3
1102 975 2011 4
1102 3870 2011 9
1102 430 2011 10
1102 916 2011 11
1102 550 2011 12查询结果
卡号 总获取 总兑换 剩余 年份 月份
1102 17174 12957 4217 2011 8
1102 17514 12957 4557 2011 9
1102 18194 16827 1367 2011 10
from 获取表 a left join 兑换表 b on a.卡号=b.卡号 and a.年份=b.年份 and a.月份=b.月份
group by a.卡号,a.年份,a.月份
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,12select a.[卡号],a.总获取,b.总兑换,剩余=a.总获取-b.总兑换,b.[年份],b.[月份]
from
(
select [卡号],总获取=sum([获取值]),[年份],[月份] from [获取表] group by [卡号],[年份],[月份]
) a,
(
select [卡号],总兑换=sum([兑换值]),[年份],[月份] from [兑换表] group by [卡号],[年份],[月份]
) b
where a.[卡号]=b.[卡号] and a.[年份]=b.[年份] and a.[月份]=b.[月份]
and a.[年份]=2011
/*
卡号 总获取 总兑换 剩余 年份 月份
----------- ----------- ----------- ----------- ----------- -----------
1102 7375 1512 5863 2011 1
1102 1060 8550 -7490 2011 3
1102 2650 975 1675 2011 4
1102 340 3870 -3530 2011 9
1102 680 430 250 2011 10
1102 680 916 -236 2011 11(6 行受影响)*/
(select a.卡号,sum(a.获取值) as 总获取,sum(b.兑换值) as 总兑换,a.年份,a.月份
from 获取表 a left join 兑换表 b on a.卡号=b.卡号 and a.年份=b.年份 and a.月份=b.月份
group by a.卡号,a.年份,a.月份) as t1