----------------------------
-- 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 all
select 101,'fee2',$100,'2012/2/1' union all
select 101,'fee3',$100,'2012/3/1' union all
select 101,'fee4',$100,'2012/4/1' union all
select 101,'fee5',$100,'2012/5/1' union all
select 102,'fee1',$100,'2012/1/1' union all
select 102,'fee2',$100,'2012/2/1' union all
select 102,'fee3',$100,'2012/3/1' union all
select 102,'fee4',$100,'2012/4/1' union all
select 102,'fee5',$100,'2012/5/1' union all
select 103,'fee1',$100,'2012/1/1' union all
select 103,'fee2',$100,'2012/2/1' union all
select 103,'fee3',$100,'2012/3/1' union all
select 103,'fee4',$100,'2012/4/1' union all
select 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 all
select 102,$210 union all
select 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.00
101         fee2 0.00
101         fee3 80.00
101         fee4 180.00
101         fee5 280.00
102         fee1 0.00
102         fee2 0.00
102         fee3 90.00
102