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