SqlServer共用表达式（CTE）With As 处理递归查询

共用表表达式（CTE）可以看成是一个临时的结果集，可以再SELECT，INSERT，UPDATE，DELETE，MARGE语句中多次引用。

一好处：使用共用表表达式可以让语句更加清晰简练。

  1.可以定义递归公用表表达式(CTE)

  2.当不需要将结果集作为视图被多个地方引用时，CTE可以使其更加简洁　　

  3.GROUP BY语句可以直接作用于子查询所得的标量列

  4.可以在一个语句中多次引用公用表表达式(CTE)

二定义：公用表达式的定义非常简单，只包含三部分：

1.   公用表表达式的名字（在WITH之后）
2.   所涉及的列名（可选）
3.   一个SELECT语句(紧跟AS之后)

    在MSDN中的原型：

WITH expression_name [ ( column_name [,...n] ) ]

 

AS

 

( CTE_query_definition )

 按照是否递归，可以将公用表（CTE）表达式分为递归公用表表达式和非递归公用表表达式.

非递归公用表表达式(CTE)

非递归公用表表达式（CTE）是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE

   非递归公用表表达式（CTE）的使用方式和视图以及子查询一致

1 2 3 4 5

WITH cte_Test AS ( SELECT * FROM dbo.SysOrganization ) SELECT * FROM cte_Test

 公用表表达式的好处之一是可以在接下来一条语句中多次引用:

只能接下来一条使用:

由于CTE只能在接下来一条语句中使用，因此，当需要接下来的一条语句中引用多个CTE时，可以定义多个，中间用逗号分隔：

递归公用表表达式(CTE)

递归公用表表达式很像派生表（Derived Tables ），指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是，CTE可以在一次定义多次进行派生递归.对于递归的概念，是指一个函数或是过程直接或者间接的调用其自身，递归的简单概念图如下:

对于递归公用表达式来说，实现原理也是相同的，同样需要在语句中定义两部分：

•    基本语句
•    递归语句

   在SQL这两部分通过UNION ALL连接结果集进行返回:

当然，越强大的力量，就需要被约束.如果使用不当的话，递归CTE可能会出现无限递归。从而大量消耗SQL Server的服务器资源.因此，SQL Server提供了OPTION选项，可以设定最大的递归次数:

   还是上面那个语句，限制了递归次数:

2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：

ith
cte1 as
(
    select * from table1 where name like 'abc%'
),
cte2 as
(
    select * from table2 where id > 20
),
cte3 as
(
    select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

3. 不能在 CTE_query_definition 中使用以下子句：

（1）COMPUTE 或 COMPUTE BY
 
（2）ORDER BY（除非指定了 TOP 子句）
 
（3）INTO
 
（4）带有查询提示的 OPTION 子句
 
（5）FOR XML
 
（6）FOR BROWSE

4.如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下面的SQL所示：

declare @s nvarchar(3)
set @s = 'C%'
;  -- 必须加分号
with
t_tree as
(
    select CountryRegionCode from person.CountryRegion where Name like @s
)
select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下面的SQL所示：

declare @s nvarchar(3)
set @s = 'C%'
;  -- 必须加分号
with
t_tree as
(
    select CountryRegionCode from person.CountryRegion where Name like @s
)
select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

最后贴出一个递归相关的语句

WITH CTE_Organization([SpClassifyId],[ParentClassifyId],[ClassifyName],ClassifyStatus,IsSiteInspection,IsPerformance,IsAfterPerformance,IsPmInterview,Iscbfy,IsStorage,IsRequired )
 AS 
 (SELECT [SpClassifyId],[ParentClassifyId],[ClassifyName],ClassifyStatus,IsSiteInspection,IsPerformance,IsAfterPerformance,IsPmInterview,Iscbfy,IsStorage,IsRequired 
 FROM dbo.SM_SupplierClassify 
 WHERE [ClassifyName] like '%电梯工程%' and ClassifyStatus='Active' 
 UNION ALL 
 SELECT o.[SpClassifyId],o.[ParentClassifyId],o.[ClassifyName],o.ClassifyStatus,o.IsSiteInspection,o.IsPerformance,o.IsAfterPerformance,o.IsPmInterview,o.Iscbfy,o.IsStorage,o.IsRequired 
 FROM dbo.SM_SupplierClassify o INNER JOIN CTE_Organization oo ON o.[SpClassifyId]=oo.[ParentClassifyId]) 
 SELECT distinct * FROM CTE_Organization

 上述递归相关的表脚本为：

CREATE TABLE [dbo].[SM_SupplierClassify](
    [SpClassifyId] [varchar](100) NOT NULL,
    [ClassifyName] [nvarchar](50) NOT NULL,
    [ClassifyFullName] [nvarchar](100) NULL,
    [ContactPerson] [nvarchar](50) NULL,
    [ContactPhone] [varchar](50) NULL,
    [ParentClassifyId] [varchar](100) NULL,
    [ClassifyLevel] [int] NULL,
    [FullName] [nvarchar](200) NULL,
    [FullId] [varchar](2000) NULL,
    [ClassifyStatus] [varchar](50) NULL,
    [ClassifyRemark] [nvarchar](200) NULL,
    [IsSiteInspection] [bit] NULL,
    [IsPerformance] [bit] NULL,
    [IsAfterPerformance] [bit] NULL,
    [IsPmInterview] [bit] NULL,
    [SeqNo] [int] NULL,
    [Isdeleted] [bit] NULL,
    [CreateUserId] [int] NULL,
    [CreateUserName] [nvarchar](50) NULL,
    [CreateDate] [datetime] NULL,
    [ModifyUserId] [int] NULL,
    [ModifyUserName] [nvarchar](50) NULL,
    [ModifyDate] [datetime] NULL,
    [ForefatherClassifyName] [nvarchar](50) NULL,
    [SupplierClassNo] [nvarchar](100) NULL,
    [Iscbfy] [bit] NULL,
    [IsStorage] [bit] NULL,
    [IsRequired] [bit] NULL,
    [IsMoreContract] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
    [SpClassifyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 数据为：https://files.cnblogs.com/files/chenwolong/%E6%95%B0%E6%8D%AE.zip

@陈卧龙的博客