对CTE递归 的理解

CREATE TABLE #D(A CHAR(1),B CHAR(2)) INSERT INTO #DSELECT 'a','b' UNION ALL SELECT 'b','c'UNION ALL SELECT 'c','d' UNION ALL SELECT 'd','e'UNION ALL SELECT 'b','m'  WITH CTEA AS(SELECT B FROM #D WHERE A = 'a'UNION ALLSELECT m.B FROM #D m , CTEA n where m.A = n.B)SELECT * FROM CTEA

如上，可用CTE轻松解决递归问题。再如下，用临时表来类比CTE中的层层数据获取。

CREATE  PROCEDURE dbo.getChildren(@D CHAR(2)) AS   CREATE TABLE #X (B CHAR(5),ID INT )INSERT #X  SELECT B,1 FROM #D where A = @D DECLARE @ID INT WHILE @@ROWCOUNT > 0BEGINSELECT @ID = MAX(ID) FROM #X INSERT #X  SELECT m.B,@ID+1 FROM #D m,#X n WHERE m.A=n.B AND n.ID = @ID  ENDSELECT * FROM #X ORDER BY ID DROP TABLE #X exec dbo.getChildren 'a' 

在利用CTE时，最重要的是要能理解它是将每次获取 的新的子层结果集作为新的查询条件~