»¨ÁË°ëÌìд³öÀ´µÄ¸ösqlÓï¾ä£¬·ÖÏíÏÂ

Declare @T Table(
    CFF_Loanno   nvarchar(100),
    BANK_Name_2  nvarchar(100),
    CFF_code  nvarchar(100),
    CFF_date   datetime,
    CFF_amt  decimal(18, 6),
    CFF_repay_date  datetime,
    COMP_Name_2   nvarchar(100)
)
---ÁÙʱ±í£¬´æ·Å»ù´¡Êý¾Ý
insert into @T(CFF_Loanno ,
    BANK_Name_2  ,
    CFF_code,
    CFF_date,
    CFF_amt,
    CFF_repay_date,
    COMP_Name_2)
SELECT A.CFF_Loanno,B.BANK_Name_2,A.CFF_code,A.CFF_date,A.CFF_amt,A.CFF_repay_date,C.COMP_Name_2
from cashflow_financing as A
inner join Bank as B on
A.CFF_Bank = B.BANK_Code
inner join Company as C on
A.CFF_repay_company =  C.COMP_Code
---С¼Æ±í£¬°´ÕÕ»¹¿îÈÕÆÚ»ã×Ü
select  N'B' as ord,N'С¼Æ' as class,
CFF_Loanno as CFF_Loanno ,
CONVERT(varchar(100), CFF_repay_date, 23) as BANK_Name_2  ,
     NULL as CFF_code,
     NULL as CFF_date,
    isnull((select sum(CFF_amt) from @T as a where a.CFF_Loanno = b.CFF_Loanno and a.CFF_repay_date = b.CFF_repay_date), 0) as CFF_amt,
     NULL as CFF_repay_date,
     NULL as COMP_Name_2
 from @T as b group by CFF_Loanno, CFF_repay_date
union all
---Ã÷ϸ±í
select N'A' as ord,N'Ã÷ϸ' as class,c.*
from @T as c
union all
---ºÏ¼Æ±í
select  N'C' as ord,N'ºÏ¼Æ' as class,
CFF_Loanno as CFF_Loanno ,
     NULL as BANK_Name_2  ,
     NULL as CFF_code,
     NULL as CFF_date,
    isnull((select sum(CFF_amt) from @T as d where d.CFF_Loanno = e.CFF_Loanno), 0) as CFF_amt,
     NULL as CFF_repay_date,
     NULL as COMP_Name_2
 from @T as e