共用表表达式(CTE)可以看成是一个临时的结果集,可以再SELECT,INSERT,UPDATE,DELETE,MARGE语句中多次引用。
一好处:使用共用表表达式可以让语句更加清晰简练。
1.可以定义递归公用表表达式(CTE)
2.当不需要将结果集作为视图被多个地方引用时,CTE可以使其更加简洁
3.GROUP BY语句可以直接作用于子查询所得的标量列
4.可以在一个语句中多次引用公用表表达式(CTE)
二定义:公用表达式的定义非常简单,只包含三部分:
- 公用表表达式的名字(在WITH之后)
- 所涉及的列名(可选)
- 一个SELECT语句(紧跟AS之后)
在MSDN中的原型:
WITH expression_name [ ( column_name [,...n] ) ]
AS ( CTE_query_definition ) |
按照是否递归,可以将公用表(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
@陈卧龙的博客