日期：2012-08-03 12:46:00 来源：本站整理
关于SQL中CTE（公用表表达式）（Common Table Expression）的总结[MSSQL防范]
本文“关于SQL中CTE（公用表表达式）（Common Table Expression）的总结[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
一．WITH AS的含义
WITH AS短语,也叫做子查询部份（subquery factoring）,可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到.有的时刻,是为了让SQL语句的可读性更高些,也有大概是在UNION ALL的差别部份,作为供应数据的部份.
分外关于UNION ALL对比有效.因为UNION ALL的每个部份大概相同,但是假如每个部份都去履行一遍的话,则本钱太高,所以可以利用WITH AS短语,则只要履行一遍便可.假如WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获得的数据放入一个TEMP表里,假如只是被调用一次,则不会.而提醒materialize则是强迫将WITH AS短语里的数据放入一个全局暂时表里.很多查询通过这种办法都可以提高速度.
二．利用办法
先看下面一个嵌套的查询语句:
复制代码 代码以下:
select * from person.StateProvince where CountryRegionCode in
(select CountryRegionCode from person.CountryRegion where Name like 'C%')
上面的查询语句利用了一个子查询.固然这条SQL语句并不复杂,但假如嵌套的层次过量,会使SQL语句非常难以阅读和保护.因此,也可以利用表变量的方法来办理这个问题,SQL语句以下:
复制代码 代码以下:
declare @t table(CountryRegionCode nvarchar(3))
insert into @t(CountryRegionCode) (select CountryRegionCode from person.CountryRegion where Name like 'C%')
select * from person.StateProvince where CountryRegionCode
in (select * from @t)
固然上面的SQL语句要比第一种方法更复杂,但却将子查询放在了表变量@t中,这样做将使SQL语句更简单保护,但又会带来另一个问题,就是性能的丧失.由于表变量实际上利用了暂时表,从而增添了额外的I/O开销,因此,表变量的方法并不太合适数据量大且频繁查询的情形.为此,在SQL Server 2005中供应了别的一种办理筹划,这就是公用表表达式（CTE）,利用CTE,可以使SQL语句的可保护性,同时,CTE要比表变量的效率高得多.
下面是CTE的语法:
复制代码 代码以下:
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
目前利用CTE来办理上面的问题,SQL语句以下:
复制代码 代码以下:
with
cr as
(
select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.StateProvince where CountryRegionCode in (select * from cr)
此中cr是一个公用表表达式,该表达式在利用上与表变量近似,只是SQL Server 2005在处理公用表表达式的方法上有所差别.
在利用CTE时应注意以下几点:
1. CTE背面必须直接跟利用CTE的SQL语句（如select、insert、update等）,不然,CTE将失效.以下面的SQL语句将无法正常利用CTE:
复制代码 代码以下:
with
cr as
(
select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.CountryRegion -- 应将这条SQL语句去掉
-- 利用CTE的SQL语句应紧跟在相关的CTE背面 --
select * from person.StateProvince where CountryRegionCode in (select * from cr)
2. CTE背面也可以跟其他的CTE,但只能利用一个with,多个CTE中间用逗号（,）脱离,以下面的SQL语句所示:
复制代码 代码以下:
with
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的表达式名称与某个数据表或视图重名,则紧跟在该CTE背面的SQL语句利用的仍旧是CTE,当然,背面的SQL语句利用的就是数据表或视图了,以下面的SQL语句所示:
复制代码 代码以下:
-- table1是一个实际存在的表
with
table1 as
(
select * from persons where age < 30
)
select * from table1 -- 利用了名为table1的大众表表达式
select * from table1 -- 利用了名为table1的数据表
4. CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE.不答应前向引用.
5. 不能在 CTE_query_definition 中利用以下子句:
（1）COMPUTE 或 COMPUTE BY
（2）ORDER BY（除非指定了 TOP 子句）
（3）INTO
（4）带有查询提醒的 OPTION 子句
（5）FOR XML
（6）FOR BROWSE
6. 假如将 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语句外,还可以举行递归调用.
微软从SQl2005起引入了CTE(Common Table Expression)以强化T-SQL.这是一个近似于非长期视图的好东东.
按照MSDN介绍
1、公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的履行范围内定义的暂时后果集.CTE 与派生表近似,具体表目前不存储为对象,并且只在查询期间有效.与派生表的差别之处在于,CTE 可自引用,还可在同一查询中引用多次.
CTE 可用于:
成立递归查询.有关具体信息,请参阅利用公用表表达式的递归查询.
在不需求通例利用视图时替换视图,也就是说,不必将定义存储在元数据中.
启用按从标量嵌套 select 语句派生的摆列行分组,大概按不肯定性函数或有外部拜候的函数举行分组.
在同一语句中多次引用生成的表.
利用 CTE 可以得到提高可读性和轻松保护复杂查询的长处.查询可以分为单独块、简单块、逻辑生成块.之后,这些简单块可用于生成更复杂的暂时 CTE,直到生成终究后果集.可以在用户定义的例程（如函数、存储历程、触发器或视图）中定义 CTE.
2、公用表表达式 (CTE) 具有一个重要的长处,那就是可以引用其自身,从而成立递归 CTE.递归 CTE 是一个反复履行初始 CTE 以返回数据子集直到获得完好后果集的公用表表达式.当某个查询引用递归 CTE 时,它即被称为递归查询.递归查询普通用于返回分层数据,比方:显示某个组织图中的雇员或物料清单筹划（此中父级产品有一个或多个组件,而那些组件大概还有子组件,大概是其他父级产品的组件）中的数据.
递 归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码.在 SQL Server 的早期版本中,递归查询普通需求利用暂时表、游标和逻辑来掌握递归步骤流.有关公用表表达式的具体信息,请参阅利用公用表表达式.
这里举例阐明以下:
为了描写便利,邀月特地摆列了一个常见的自关联Table
表构造以下:
表构造
复制代码 代码以下:
CREATE TABLE [dbo].[CategorySelf](
[PKID] [int] IDENTITY(1,1) NOT NULL,
[C_Name] [nvarchar](50) NOT NULL,
[C_Level] [int] NOT NULL,
[C_Code] [nvarchar](255) NULL,
[C_Parent] [int] NOT NULL,
[InsertTime] [datetime] NOT NULL,
[InsertUser] [nvarchar](50) NULL,
[UpdateTime] [datetime] NOT NULL,
[UpdateUser] [nvarchar](50) NULL,
[SortLevel] [int] NOT NULL,
[CurrState] [smallint] NOT NULL,
[F1] [int] NOT NULL,
[F2] [nvarchar](255) NULL
CONSTRAINT [PK_OBJECTCATEGORYSELF] PRIMARY KEY CLUSTERED
(
[PKID] 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
再插入一些测试数据
复制代码 代码以下:
Insert
INSERT INTO [CategorySelf]([C_Name],[C_Level] ,[C_Code],[C_Parent] ,[InsertTime] ,[InsertUser] ,[UpdateTime] ,[UpdateUser] ,[SortLevel] ,[CurrState] ,[F1] ,[F2])
select '分类1',1,'0',0,GETDATE(),'testUser',DATEADD(dd,1,getdate()),'CrackUser',13,0,1,'邀月备注' union all
select '分类2',1,'0',0,GETDATE(),'testUser',DATEADD(dd,78,getdate()),'CrackUser',12,0,1,'邀月备注' union all
select '分类3',1,'0',0,GETDATE(),'testUser',DATEADD(dd,6,getdate()),'CrackUser',10,0,1,'邀月备注' union all
select '分类4',2,'1',1,GETDATE(),'testUser',DATEADD(dd,75,getdate()),'CrackUser',19,0,1,'邀月备注' union all
select '分类5',2,'2',2,GETDATE(),'testUser',DATEADD(dd,3,getdate()),'CrackUser',17,0,1,'邀月备注' union all
select '分类6',3,'1/4',4,GETDATE(),'testUser',DATEADD(dd,4,getdate()),'CrackUser',16,0,1,'邀月备注' union all
select '分类7',3,'1/4',4,GETDATE(),'testUser',DATEADD(dd,5,getdate()),'CrackUser',4,0,1,'邀月备注' union all
select '分类8',3,'2/5',5,GETDATE(),'testUser',DATEADD(dd,6,getdate()),'CrackUser',3,0,1,'邀月备注' union all
select '分类9',4,'1/4/6',6,GETDATE(),'testUser',DATEADD(dd,7,getdate()),'CrackUser',5,0,1,'邀月备注' union all
select '分类10',4,'1/4/6',6,GETDATE(),'testUser',DATEADD(dd,7,getdate()),'CrackUser',63,0,1,'邀月备注' union all
select '分类11',4,'1/4/6',6,GETDATE(),'testUser',DATEADD(dd,8,getdate()),'CrackUser',83,0,1,'邀月备注' union all
select '分类12',4,'2/5/8',8,GETDATE(),'testUser',DATEADD(dd,10,getdate()),'CrackUser',3,0,1,'邀月备注' union all
select '分类13',4,'2/5/8',8,GETDATE(),'testUser',DATEADD(dd,15,getdate()),'CrackUser',1,0,1,'邀月备注'
一个典型的利用场景是:在这个自关联的表中,查询以PKID为2的分类包含全部子分类.大概很多情形下,我们不得不用暂时表/表变量/游标等.目前我们有了CTE,就简单多了
复制代码 代码以下:
CTEDemo1
WITH SimpleRecursive(C_Name, PKID, C_Code,C_Parent)
AS
(SELECT C_Name, PKID, C_Code,C_Parent FROM CategorySelf WHERE PKID = 2
UNION ALL
SELECT p.C_Name, p.PKID, p.C_Code,p.C_parent
FROM CategorySelf P INNER JOIN
SimpleRecursive A ON A.PKID = P.C_Parent
)
SELECT sr.C_Name as C_Name, c.C_Name as C_ParentName,sr.C_Code as C_ParentCode
FROM SimpleRecursive sr inner join CategorySelf c
on sr.C_Parent=c.PKID
查询后果以下:C_Name C_ParentName C_ParentCode
分类5 分类2 2
分类8 分类5 2/5
分类12 分类8 2/5/8
分类13 分类8 2/5/8
感受怎么样?假如我只想查询第二层,而不是默许的无限查询下去,
可以在上面的SQL后加一个选项 Option(MAXRECURSION 5),注意5表示到第5层就不往下找了.假如只想找第二层,但实际后果有三层,此时会出错,
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.
此时可以通过where条件来办理,而保证不出错,看以下SQL语句:
CTEDemo2
复制代码 代码以下:
WITH SimpleRecursive(C_Name, PKID, C_Code,C_Parent,Sublevel)
AS
(SELECT C_Name, PKID, C_Code,C_Parent,0 FROM CategorySelf WHERE PKID = 2
UNION ALL
SELECT p.C_Name, p.PKID, p.C_Code,p.C_parent,Sublevel+1
FROM CategorySelf P INNER JOIN
SimpleRecursive A ON A.PKID = P.C_Parent
)
SELECT sr.C_Name as C_Name, c.C_Name as C_ParentName,sr.C_Code as C_ParentCode
FROM SimpleRecursive sr inner join CategorySelf c
on sr.C_Parent=c.PKID
where SubLevel<=2
查询后果:
C_Name C_ParentName C_ParentCode
分类5 分类2 2
分类8 分类5 2/5
当然,我们不是说CTE就是万能的.通过好的表计划也可以某种程度上办理特定的问题.下面用通例的SQL实现上面这个需求.
注意:上面表中有一个字段很重要,就是C_Code,编码 ,格局如"1/2","2/5/8"表示该分类的上级分类是1/2,2/5/8
这样,我们查询就简单多,查询以PKID为2的分类包含全部子分类:
复制代码 代码以下:
SELECT C_Name as C_Name, (Select top 1 C_Name from CategorySelf s where c.C_Parent=s.PKID) as C_ParentName,C_Code as C_ParentCode
from CategorySelf c where C_Code like '2/%'
查询以PKID为2的分类包含全部子分类,且级别不大于3
复制代码 代码以下:
SELECT C_Name as C_Name, (Select top 1 C_Name from CategorySelf s where c.C_Parent=s.PKID) as C_ParentName,C_Code as C_ParentCode
from CategorySelf c where C_Code like '2/%' and C_Level<=3
查询后果同上,略去.这里我们看出,有时刻,好的表构造计划相当重要.
邀月于2009.10.23 1:36 完成份享.
有人很关心性能问题.目前没有测试过.稍后会附上百万级测试报告.不过,有两点理解邀月忘了增补:
1、CTE其实是面向对象的,运行的底子是CLR.一个很好的阐明是With查询语句中是辨别字段的大小写的.即"C_Code"和"c_Code"是不一样的,后者会报错.这与普通的SQL语句差别.
2、 这个利用示例重在简化业务逻辑,即就是性能不佳,但对暂时表/表变量/游标等传统处理方法是一种业务层次上的简化大概说是优化.
公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的履行范围内定义的暂时后果集.CTE 与派生表近似,具体表目前不存储为对象,并且只在查询期间有效.与派生表的差别之处在于,CTE 可自引用,还可在同一查询中引用多次.
CTE 可用于:
成立递归查询.
在不需求通例利用视图时替换视图,也就是说,不必将定义存储在元数据中.
启用按从标量嵌套 select 语句派生的摆列行分组,大概按不肯定性函数或有外部拜候的函数举行分组.
在同一语句中多次引用生成的表.
利用 CTE 可以得到提高可读性和轻松保护复杂查询的长处.查询可以分为单独块、简单块、逻辑生成块.之后,这些简单块可用于生成更复杂的暂时 CTE,直到生成终究后果集.
可以在用户定义的例程（如函数、存储历程、触发器或视图）中定义 CTE.
CTE 的构造
CTE 由表示 CTE 的表达式名称、可选列列表和定义 CTE 的查询构成.定义 CTE 后,可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其举行引用,就像引用表或视图一样.CTE 也可用于 CREATE VIEW 语句,作为定义 SELECT 语句的一部份.
CTE 的基本语法构造以下:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
只有在查询定义中为全部后果列都供应了差别的名称时,列名称列表才是可选的.
运行 CTE 的语句为:
SELECT <column_list>
FROM expression_name
示例:
复制代码 代码以下:
with s_name as
(
select s.name, sc.c,sc.grade from SQL Server student AS s,sc
where s.s#=sc.s#
)
select * from s_name
在利用CTE时应注意以下几点:
1. CTE背面必须直接跟利用CTE的SQL语句（如select、insert、update等）,不然,CTE将失效.
2. CTE背面也可以跟其他的CTE,但只能利用一个with,多个CTE中间用逗号（,）脱离.
3. 假如CTE的表达式名称与某个数据表或视图重名,则紧跟在该CTE背面的SQL语句利用的仍旧是CTE,当然,背面的SQL语句利用的就是数据表或视图了.
4. CTE 可以引用自身（递归查询）,也可以引用在同一 WITH 子句中预先定义的 CTE.不答应前向引用.
5. 不能在 CTE_query_definition 中利用以下子句:
（1）COMPUTE 或 COMPUTE BY
（2）ORDER BY（除非指定了 TOP 子句）
（3）INTO
（4）带有查询提醒的 OPTION 子句
（5）FOR XML
（6）FOR BROWSE
6. 假如将 CTE 用在属于批处理的一部份的语句中,那么在它之前的语句必须以分号末尾.
以上是“关于SQL中CTE（公用表表达式）（Common Table Expression）的总结[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
100%(1) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“关于SQL中CTE（公用表表达式）（Common Table Expression）的总结”更多相关内容
中查找“关于SQL中CTE（公用表表达式）（Common Table Expression）的总结”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论