SQL-with as基本用法(源码DEMO)

 DROP TABLE #temp;
with 
cr as 
( 
SELECT    At.SysNo AS AtSysNo ,
                    ( CASE WHEN At.Source = 1 THEN At.VendorSysNo
                           ELSE At.DistributorSysNo
                      END ) AS VdSysNo ,
                    At.Source ,
                    At.BizTypeCode ,
                    BT.Name ,
                    ABC.*
          FROM      BBCAccount.dbo.Account (NOLOCK) At
                    LEFT JOIN ( SELECT  *
                                FROM    BBCAccount.dbo.BusinessType (NOLOCK) BT
                                WHERE   BT.ParentCode IS NULL
                                        AND Status = 1
                                        AND BT.IsSystem = 1
                                        AND BT.SysNo IS NOT NULL
                              ) AS BT ON At.BizTypeCode = BT.Code
                    LEFT JOIN ( SELECT  ABC.SysNo ,
                                        ABC.BizTypeCode AS ABCBTC ,
                                        ABC.AccountSysNo ,
                                        ABC.BizID ,
                                        ABC.Balance ,
                                        ABC.AdjustAmount ,
                                        ABC.AdjustType ,
                                        ABC.AdjustedBalance ,
                                        ABC.InDate
                                FROM    BBCFinance.dbo.AccountBalanceChange (NOLOCK) ABC
                                        LEFT JOIN BBCFinance.dbo.AccountAdjustReceipt (NOLOCK)AAR  ON AAR.AccountSysNo = ABC.AccountSysNo      AND AAR.ChangeNo = ABC.BizID AND AAR.Status=2
                              
                                      
                              ) AS ABC ON ABC.AccountSysNo = At.SysNo 
          
	 ) 
	 SELECT * INTO #temp FROM cr 




SELECT * FROM #temp WHERE    VdSysNo=2384



SELECT TE.* FROM   #temp TE
LEFT JOIN 
 (
SELECT VdSysNo,Source, Name,MAX(InDate) AS InDate FROM #temp   WHERE VdSysNo=2384     GROUP  BY VdSysNo,Source,Name )
AS TT ON TT.VdSysNo=TE.VdSysNo AND TT.Source = TE.Source AND TT.InDate = TE.InDate AND TT.Name = TE.Name
WHERE TE.VdSysNo=2384