Feature:
- 公用表表达式只能包含一个SELECT,多SELECT需UNION,UNION ALL
- 公用表表达式只能引用一次
- 公用表表达式可以包括对自身的引用,这种表达式称为递归公用表表达式
--临时表变量
WITH t_Class
AS
(
SELECT *
FROM Intranet..TemplateDocClass
WHERE State = 1
)
SELECT COUNT(1)
FROM t_Class --引用自身,递归多级别
WITH t_Class
AS
(
SELECT ID,ClassName,1 AS [Deep]
FROM Intranet..TemplateDocClass
WHERE ParentID = -1 UNION ALL SELECT C1.ID,C1.ClassName,C2.Deep + 1
FROM Intranet..TemplateDocClass AS C1
INNER JOIN t_Class AS C2 ON C2.ID = C1.ParentID
)
SELECT *
FROM t_Class