求教费用计算的SQL语句
我现在有两个表
第一个表是
学号 费用名称 数额 应交费的日期
101 fee1 $100 2012/1/1
101 fee2 $100 2012/2/1
101 fee3 $100 2012/3/1
101 fee4 $100 2012/4/1
101 fee5 $100 2012/5/1
102 fee1 $100 2012/1/1
102 fee2 $100 2012/2/1
102 fee3 $100 2012/3/1
102 fee4 $100 2012/4/1
102 fee5 $100 2012/5/1
103 fee1 $100 2012/1/1
103 fee2 $100 2012/2/1
103 fee3 $100 2012/3/1
103 fee4 $100 2012/4/1
103 fee5 $100 2012/5/1
第二个表是
学号 已经付的费用
101 $220
102 $210
103 $330
请教如何写SQL语句，才能得到下面这样的结果啊？
我的目标是形成第三个表
第三个表是
学号 费用名称 数额 应交费的日期
101 fee1 $0 2012/1/1
101 fee2 $0 2012/2/1
101 fee3 $80 2012/3/1
101 fee4 $100 2012/4/1
101 fee5 $100 2012/5/1
102 fee1 $0 2012/1/1
102 fee2 $0 2012/2/1
102 fee3 $90 2012/3/1
102 fee4 $100 2012/4/1
102 fee5 $100 2012/5/1
103 fee1 $0 2012/1/1
103 fee2 $0 2012/2/1
103 fee3 $0 2012/3/1
103 fee4 $70 2012/4/1
103 fee5 $100 2012/5/1
[解决办法]
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-06-21 09:30:54-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[a]if object_id('[a]') is not null drop table [a]go create table [a]([学号] int,[费用名称] varchar(4),[数额] money,[应交费的日期] datetime)insert [a]select 101,'fee1',$100,'2012/1/1' union allselect 101,'fee2',$100,'2012/2/1' union allselect 101,'fee3',$100,'2012/3/1' union allselect 101,'fee4',$100,'2012/4/1' union allselect 101,'fee5',$100,'2012/5/1' union allselect 102,'fee1',$100,'2012/1/1' union allselect 102,'fee2',$100,'2012/2/1' union allselect 102,'fee3',$100,'2012/3/1' union allselect 102,'fee4',$100,'2012/4/1' union allselect 102,'fee5',$100,'2012/5/1' union allselect 103,'fee1',$100,'2012/1/1' union allselect 103,'fee2',$100,'2012/2/1' union allselect 103,'fee3',$100,'2012/3/1' union allselect 103,'fee4',$100,'2012/4/1' union allselect 103,'fee5',$100,'2012/5/1'--> 测试数据：[b]if object_id('[b]') is not null drop table [b]go create table [b]([学号] int,[已经付的费用] money)insert [b]select 101,$220 union allselect 102,$210 union allselect 103,$330--------------开始查询--------------------------select t.学号,费用名称,数额=case when isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用>0 then isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用 else 0 end from a t join b on t.学号=b.学号----------------结果----------------------------/* (15 行受影响)(3 行受影响)学号 费用名称 数额----------- ---- ---------------------101 fee1 0.00101 fee2 0.00101 fee3 80.00101 fee4 180.00101 fee5 280.00102 fee1 0.00102 fee2 0.00102 fee3 90.00102 fee4 190.00102 fee5 290.00103 fee1 0.00103 fee2 0.00103 fee3 0.00103 fee4 70.00103 fee5 170.00(15 行受影响)*/
[解决办法]
select t.学号,费用名称, 数额= case when isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用>0 then isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用 when isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用>100 then 100 else 0 end from a t join b on t.学号=b.学号
[解决办法]
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-06-21 09:30:54-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[a]if object_id('[a]') is not null drop table [a]go create table [a]([学号] int,[费用名称] varchar(4),[数额] money,[应交费的日期] datetime)insert [a]select 101,'fee1',$100,'2012/1/1' union allselect 101,'fee2',$100,'2012/2/1' union allselect 101,'fee3',$100,'2012/3/1' union allselect 101,'fee4',$100,'2012/4/1' union allselect 101,'fee5',$100,'2012/5/1' union allselect 102,'fee1',$100,'2012/1/1' union allselect 102,'fee2',$100,'2012/2/1' union allselect 102,'fee3',$100,'2012/3/1' union allselect 102,'fee4',$100,'2012/4/1' union allselect 102,'fee5',$100,'2012/5/1' union allselect 103,'fee1',$100,'2012/1/1' union allselect 103,'fee2',$100,'2012/2/1' union allselect 103,'fee3',$100,'2012/3/1' union allselect 103,'fee4',$100,'2012/4/1' union allselect 103,'fee5',$100,'2012/5/1'--> 测试数据：[b]if object_id('[b]') is not null drop table [b]go create table [b]([学号] int,[已经付的费用] money)insert [b]select 101,$220 union allselect 102,$210 union allselect 103,$330--------------开始查询--------------------------select t.学号,费用名称, 数额= case when isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用>0 and isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用<100 then isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用 when isnull((select sum(数额) from a where 学号=t.学号 and 应交费的日期<=t.应交费的日期),0)-b.已经付的费用>=100 then 100 else 0 end from a t join b on t.学号=b.学号----------------结果----------------------------/* ((15 行受影响)(3 行受影响)学号 费用名称 数额----------- ---- ---------------------101 fee1 0.00101 fee2 0.00101 fee3 80.00101 fee4 100.00101 fee5 100.00102 fee1 0.00102 fee2 0.00102 fee3 90.00102 fee4 100.00102 fee5 100.00103 fee1 0.00103 fee2 0.00103 fee3 0.00103 fee4 70.00103 fee5 100.00(15 行受影响)*/
[解决办法]