求一条sql语句写法

[觉v醒]

求一条sql语句效率写法 2张表 发票表t_fp(主要字段 fpiD-发票ID，fpAmount 发票金额，fpDate--发票时间)
收款表t_sk(主要字 fpID-发票ID,skAmount-收款金额，skDate--收款时间)
发票表和收款表是一对多的关系 比如一张1w金额的发票 收款会分3次收回

发票表现有2w多条数据，收款单 有4w多条( 只运行了10个月)
--------------------
请问如何比较效率的实现如下效果
发票号码，发票金额 30天收款金额 60天收款金额 120天+收款金额 余额

[-晴天]

select a.fpid,a.fpamount,
sum(case when datediff(d,a.fpdate,b.skdate)<=30 then b.skamount else 0 end) as [30天收款金额],
sum(case when datediff(d,a.fpdate,b.skdate)<=60 then b.skamount else 0 end) as [60天收款金额],
sum(case when datediff(d,a.fpdate,b.skdate)<=120 then b.skamount else 0 end) as [120天收款金额],
a.fpamount-sum(b.fpamount) as 余额
from t_fp a inner join t_sk on a.fpid=b.fpid
group by a.fpid,a.fpamount

[--小F--]

select
 a.fpid,a.fpamount,
 sum(case when datediff(d,a.fpdate,b.skdate)<=30 then b.skamount else 0 end) as [30天收款金额],
 sum(case when datediff(d,a.fpdate,b.skdate)<=60 then b.skamount else 0 end) as [60天收款金额],
 sum(case when datediff(d,a.fpdate,b.skdate)<=120 then b.skamount else 0 end) as [120天收款金额],
isnull(a.fpamount,0)-isnull(sum(b.fpamount),0) as 余额
from
 t_fp a , t_sk b
where 
 a.fpid=b.fpid
group by
 a.fpid,a.fpamount

[NBDBA]

select 
  a.fpid as 发票号码,a.fpAmount as 发票金额
,sum(case when b.skdate < dateadd(day,30,a.fpdate) then b.skAmount else 0 end) as [30天收款金额]
,sum(case when b.skdate < dateadd(day,60,a.fpdate) then b.skAmount else 0 end) as [60天收款金额]
--,sum(case when b.skdate < dateadd(day,120,a.fpdate) then b.skAmount else 0 end) as [120天收款金额]
,sum(case when b.skdate >= dateadd(day,120,a.fpdate) then b.skAmount else 0 end) as [120天+收款金额]
,a.fpAmount - isnull(sum(b.skAmount),0) as [余额]
from t_fp a left join t_sk b
on a.fpid = b.fpid
group by a.fpid,a.fpAmount

[geniuswjt]

好久不见晴天大大[Quote=引用 1 楼 qianjin036a 的回复:]
SQL code
select a.fpid,a.fpamount,
sum(case when datediff(d,a.fpdate,b.skdate)<=30 then b.skamount else 0 end) as [30天收款金额],
sum(case when datediff(d,a.fpdate,b.skdate)<=60 then b.skamount else 0 e……
[/Quote]

[觉v醒]

谢谢各位