CTE的使用

　　CTE在SQL2005后的版本提供，丰富了查询的表现形式，下面我们慢慢来看下CTE都能干什么

　　1、自我递归　　

 

;WITH myaa AS 
(
SELECT num=1 UNION 
ALL 
SELECT num=num+1 FROM myaa WHERE num<10
)
SELECT * FROM myaa 
--OPTION (MAXRECURSION 0);--用于限制返回的层级，默认100

　　2、打印字母表

　　

;WITH myCTE AS 
(
SELECT num=65,Mcharacter=CHAR(65)
UNION ALL 
SELECT num=num+1,Mcharacter=CHAR(num+1) FROM myCTE WHERE num<90
)
--SELECT ','+Mcharacter FROM myCTE  FOR XML PATH('')
SELECT  STUFF((SELECT ','+Mcharacter FROM myCTE  FOR XML PATH('')),1,1,'')  

　　

　　3、实现Factorial

;WITH myCte AS 
(
SELECT num=1,faccode=1 
UNION ALL 
SELECT num=num+1,faccode=(num+1)*faccode  FROM myCte WHERE num<10
)
SELECT * FROM mycte

　　看完上面有的朋友要说了，上面的全没什么用啊，废材，那下面我们来看一个比较有用的场景，相信很多朋友都不会陌生

　　4、最最常用的场景

CREATE TABLE #t(id VARCHAR(20),pid VARCHAR(20),NAME VARCHAR(20))

INSERT INTO #t
SELECT '001',NULL,'广东省' UNION ALL 
SELECT '002','001','广州市' UNION ALL 
SELECT '003','001','深圳市' UNION ALL 
SELECT '004','002','天河区' UNION ALL 
SELECT '005','003','罗湖区' UNION ALL 
SELECT '006','003','福田区' UNION ALL 
SELECT '007','003','宝安区' UNION ALL 
SELECT '008','007','西乡镇' UNION ALL 
SELECT '009','007','龙华镇' UNION ALL 
SELECT '010','007','松岗镇'  
 

;WITH mycte AS 
(
SELECT id,pid,NAME,levels=0 FROM #t WHERE id='007' --CTE
UNION ALL 
--	  字段的选取同样重要
SELECT b.id,b.pid,b.NAME,levels=levels+1 FROM mycte a,#t b WHERE b.pid=a.id --通过CTE的ID与原始表的PID来匹配记录
)
SELECT * FROM mycte

　　

　　在这之前我短浅的认为CTE的用法就上面的这么几种，伴随着时间的推移我发现了它更广阔的使用场景，字符串处理、统计，它的身影可以说无处不在，在随后的备忘中我会贴出CTE在各个方面的使用。

　　顺便提下，在CTE之前，写递归就没有这么简单了，下面写个之前我常用到的方式：

DECLARE @t_level table(id varchar(30),pid VARCHAR(30),NAME VARCHAR(30), level int)
DECLARE @id VARCHAR(10),@level INT
SET @id='007';
SET @level=1;
insert into @t_level select id,pid,name,@level FROM #t WHERE id=@id

WHILE @@ROWCOUNT>0
BEGIN
	SET @level=@level+1
	INSERT INTO @t_level SELECT b.id,b.pid,b.name,@level FROM @t_level a INNER JOIN #t b on 
	b.pid=a.id AND a.level=@level-1
END 
SELECT #t.* FROM #t,@t_level WHERE #t.id=[@t_level].id