查询问题---迷糊

获取表
卡号 获取值 年份 月份
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

哪有8月份

SQL code

select a.卡号,sum(a.获取值),sum(b.兑换值),a.年份,a.月份
from 获取表 a left join 兑换表 b on a.卡号=b.卡号 and a.年份=b.年份 and a.月份=b.月份
group by a.卡号,a.年份,a.月份

我也没看见。

SQL code

--> 测试数据：[获取表]
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



select 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 行受影响)

*/

怎么个算法？ 貌似每个月都对不齐

SQL code

select t1.卡号,t1.总获取,t1.总兑换,(t1.总获取-t1.总兑换) as 剩余,t1.年份,t1.月份 from 
(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