求教费用计算的SQL语句

[wisesuperman]

我现在有两个表

第一个表是
学号 费用名称 数额 应交费的日期
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

[wisesuperman]

[Quote=引用 5 楼 的回复:]

引用 1 楼 的回复:
SQL code
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2012-06-21 09:30:54
-- Version:
-- Microsoft SQL Server ……
[/Quote]

大家真是热心，谢谢，问题用上述方法解决了！

[--小F--]

----------------------------------------------------------------
-- 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         fee4 190.00
102         fee5 290.00
103         fee1 0.00
103         fee2 0.00
103         fee3 0.00
103         fee4 70.00
103         fee5 170.00

(15 行受影响)


*/

[十三门徒]

你需要解释下数值列的变化规律
没看明白，为什么上面都是100，下面出现这个规律

[--小F--]

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.学号

[--小F--]

----------------------------------------------------------------
-- 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 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.00
101         fee2 0.00
101         fee3 80.00
101         fee4 100.00
101         fee5 100.00
102         fee1 0.00
102         fee2 0.00
102         fee3 90.00
102         fee4 100.00
102         fee5 100.00
103         fee1 0.00
103         fee2 0.00
103         fee3 0.00
103         fee4 70.00
103         fee5 100.00

(15 行受影响)


*/

[十三门徒]

[Quote=引用 1 楼 的回复:]
SQL code
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2012-06-21 09:30:54
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) -……
[/Quote]
引用1楼数据表

with tb as (
select c.*,d.已经付的费用,费用总计=(select SUM([数额]) from a e where e.学号=c.学号 and e.应交费的日期<=c.应交费的日期)  from a c
left join b d on c.学号=d.学号)

select 学号,费用名称 ,数额=(case when 费用总计-已经付的费用<0 then 0 when 费用总计-已经付的费用-数额>=0 then 数额 else 费用总计-已经付的费用 end)
,应交费的日期
 from tb