如何将select语句转换为公用表表达式（CTE） [英] How do you turn a select statement into a common table expression (CTE)

问题描述

我有以下SQL select语句 - 如何将其转换为公用表表达式（CTE）？

I have the following SQL select statement - how do I turn it into a common table expression (CTE)?

SELECT  D.AIDNO      AS RIDNO      ,
	D.ALOANUMBER AS RLOANUMBER ,
	D.AIDNOLOAN  AS RIDNOLOAN  ,
	D.ACUSTOMER  AS RCUSTOMER  ,
	SUM( CASE E.BTRANS_TYPE WHEN 'TOTAL REPAYMENT'     THEN BAMOUNT ELSE 0 END ) AS RACT_AMT  ,
	SUM( CASE E.BTRANS_TYPE WHEN 'PRINCIPAL REPAYMENT' THEN BAMOUNT ELSE 0 END ) AS RACT_PRIN ,
	SUM( CASE E.BTRANS_TYPE WHEN 'INTEREST REPAYMENT'  THEN BAMOUNT ELSE 0 END ) AS RACT_INT  ,
	SUM(D.APAY_AMT-CASE E.BTRANS_TYPE WHEN 'TOTAL REPAYMENT' THEN BAMOUNT ELSE 0 END) AS RARR_AMT,
	COUNT(D.APAY_AMT) AS RAVG_PAY  , 
	D.AINT_NO    AS RINT_NO   ,
	D.APAY_TYPE  AS RPAY_TYPE   
	FROM #VLOANDED  D  JOIN #VTRANS E
	ON 	D.AIDNOLOAN = E.BIDNOLOAN 
 GROUP BY  D.AIDNOLOAN , D.AIDNO , D.ALOANUMBER, D.ACUSTOMER,
	D.AINT_NO , D.AINT_NAME , D.ABANKCD ,  D.ABANKNAME,  
        D.APAY_TYPE
 ORDER BY  D.AIDNOLOAN

推荐答案

每个SELECT语句都可以通过put变成一个常量表表达式这里 [ ^ ]

Every SELECT statement can be turned into a constant table expression by putting the syntax around it as documented here[^]

这篇关于如何将select语句转换为公用表表达式（CTE）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！