求一ＳＱＬ　ＳＥＲＶＥＲ语句

求一ＳＱＬ　ＳＥＲＶＥＲ语句


TblMess客户表
MessNo      Mess
001         客户Ａ
002         客户Ｂ
003　　　　 客户Ｃ
SsTable(销售发货表)
SsNo               SsDate       Mess       Makeorder
Ss0808-008        2008-8-1      客户Ａ　　　Admin

SsList(销售发货明细表)　　　
SsNo        WareNo     WareName    Unit   Quantity    Price   Money
Ss0808-008   F01        内存条      条       2         130     260
Ss0808-008   F02         光驱       台       8         120     960

SrTable(销售退货)
SrNo           SrDate         Mess         MakeOrder
SR0808-001     2008-8-10      客户Ａ　　　　Admin

SrList(销售退货明细表)
SrNo         SsNo       SsDate   WareNo WareName Unit Quantity  Price      Money
Sr0808-001 Sr0808-008   2008-8-1   F01  内存条    条    1       130        130



我要的结果是这样的
MessNo    Mess   SaleTotalMoney  SaleReturnMoney   LastTotalMoney   
001      客户Ａ    1220                 130             1090
002      客户Ｂ    0                     0               0
003      客户Ｃ　　0                     0               0　　

declare @TblMess table(MessNo char(3),Mess char(20))
declare @SsTable table(SsNo char(20),Mess char(20))
declare @SsList table(SsNo char(20),Quantity smallint,Price int,Money int)
declare @SrTable table(SrNo char(20),SsNo char(20),Mess char(20))
declare @SrList table(SrNo char(20),SsNo char(20),Quantity smallint,Price int,Money int)

insert into @TblMess values('001','客户A')
insert into @TblMess values('002','客户B')
insert into @TblMess values('003','客户C')

insert into @SsTable values('Ss0808-008','客户A')

insert into @SsList values('Ss0808-008',2,130,260)
insert into @SsList values('Ss0808-008',8,120,960)

insert into @SrTable values('SR0808-001','Ss0808-008','客户A')

insert into @SrList values('SR0808-001','Ss0808-008',1,130,130)

select a.MessNo,a.Mess,isnull(e.SaleTotalMoney,0)SaleTotalMoney,isnull(f.SaleReturnMoney,0) SaleReturnMoney,isnull(e.SaleTotalMoney-f.SaleReturnMoney,0) LastTotalMoney
from @TblMess a left join @SsTable b on a.Mess=b.Mess
                left join (select SsNo,SUM(Money) SaleTotalMoney from @SsList group by SsNo)e on b.SsNo=e.SsNo
                left join (select d.SsNo,d.Money SaleReturnMoney from @SrTable c join @SrList d on c.SrNo=d.SrNo) f on b.SsNo=f.SsNo
                order by a.MessNo