在编写t-sql代码时,往往需要临时存储某些结果集。前面我们已经广泛使用和介绍了两种临时存储结果集的方法:临时表和表变量。除此之外,还可以使用公用表表达式的方法。
公用表表达式(common table expression)是sql server2005版本的引入的一个特性。cte可以看组是一个临时的结果集,可以再接下来来的一个select,insert,update,delete,merge语句中多次引用。
一、3种方法比较
使用公用表达式cte可以让语句更加清晰简练。与公用表达式作用类似的还有临时表和表变量。下面给出三种方法的对比。
- 临时表#:需要在临时数据库tempdb中通过i/o操作来创建表结构,一旦用户退出sql server环境则自动被删除。
- 表变量@:在内存中以表结构的形式存在,其定义与变量一致,其使用与表类似,不需要产生i/o。
- 公用表表达式with as:定义在内存中保存的临时存储结果集对象,不产生i/o,不需要按照表变量这样定义,使用方法和表类似。可以自己引用,也可以再查询中被多次引用。
1、使用cte好处
根据微软对cte好处的描述,可以归结为四点:
- 可以定义递归公用表表达式(cte)
- 当不需要将结果集作为视图被多个地方引用时,cte可以使其更加简洁
- group by 语句可以直接作用于子查询所得的标量列
- 可以在一个语句中多次引用公用表表达式(cte)
二、with as的含义
with as-做子查询部分(subquery factoring)。
它用于定义一个sql片段,该片段会被是整个sql语句所用到。如果with as所以定的表名被调用两次以上,则优化器会自动将with as所获取的数据放入临时表里,如果只是被调用一次,则不会。
可以通过materialize将with as短语里的数据强制放入全局临时表里。
with as可以被紧跟着的一条sql语句所使用多次,但不能被紧跟着的多条sql语句使用。
with b as ( select * from xxx where id > 5 ) select * from b
三、cte的定义
cte的定义语法如下,主要包括3个部分。
- expression_name:cte表达式的名称。
- column_name:列名列表。
- cte_query_definition:定义cte结果集的select查询语句
with expression_name [(column_name [,...n] )] as ( cte_query_definition )
按照是否递归,可以将公用表(cte)表达式分为递归公用表表达式和非递归公用表表达式.
1、非递归公用表表达式(cte):
非递归公用表表达式(cte)是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的cte。
非递归公用表表达式(cte)的使用方式和视图以及子查询一致。
比如一个简单的非递归公用表表达式:
with cte_test as ( select * from person_1 ) select * from cte_test
公用表表达式的好处之一是可以在接下来一条语句中多次引用:
with cte_test as (select * from person_1) select * from cte_test as a --第一次引用 inner join cte_test as b --第二次引用 on a.id=b.id order by a.id desc;
虽然以上引用了多次,但是只是一条语句,所以可以正常执行。
如果多条语句引用,如下面这样,是会报错的。
with cte_test as (select * from person_1) select * from cte_test; select * from cte_test;
输出结果如下:
由于cte只能在接下来一条语句中使用,因此,当需要接下来的一条语句中引用多个cte时,可以定义多个,中间用逗号分隔。下面是一次定义多个cte的例子:
with cte_test1 as (select * from person_1), cte_test2 as (select * from person_2) select * from cte_test1 union select * from cte_test2;
结果如下:
2、递归公用表表达式(cte):
对于递归公用表达式来说,只需要在语句中定义两部分:
- 基本语句
- 递归语句
先建一张表栏目表如下,栏目id,栏目名称,栏目的父栏目。
现在使用cte查询其每个栏目是第几层栏目的代码如下:
declare @table1 table(id int, name varchar(10), parentid int); insert into @table1(id, name, parentid) values(1, '国内新闻', 0), (2, '广东新闻', 1), (3, '广州新闻', 2), (4, '天河新闻', 3), (5, '山东新闻', 1), (5, '青岛新闻', 5); select * from @table1; with col_cte(id, name, parentid, tlevel) as ( --基本语句 select id, name, parentid, 0 as tlevel from @table1 where parentid=0 union all --递归语句 select c.id, c.name, c.parentid, ce.tlevel+1 as tlevel from @table1 as c inner join col_cte as ce --递归调用 on c.parentid=ce.id) select * from col_cte;
输出结果如下:
0表示顶级栏目。1就是1级栏目。语法非常优雅。就一个select * fron col_cte。这正是cte强大的地方,但是,这要有约束,否则如果无限制递归可以会消耗掉非常多的系统资源。下面来看看如何限制递归的最大次数。
如将上面的查询语法改为:
with col_cte(id,name,parentid,tlevel ) as ( --基本语句 select id,name,parentid,0 as tlevel from @table1 where parentid = 0 union all --递归语句 select c.id,c.name,c.parentid,ce.tlevel+1 as tlevel from @table1 as c inner join col_cte as ce on c.parentid = ce.id ) select * from col_cte option(maxrecursion 2) --指定最大递归次数为2
我们知道在上面的查询中,要查到天河区新闻最少要递归3次,但是现在只递归2次,运行是什么结果呢?
提示信息如下:
消息 530,级别 16,状态 1,第 1 行 语句被终止。完成执行语句前已用完最大递归 2。
cte是一种十分优雅的存在。cte所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归cte可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。
到此这篇关于sql server中t-sql公用表表达式(cte)的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持<计算机技术网(www.ctvol.com)!!>。
需要了解更多数据库技术:SQL Server使用T-SQL进阶之公用表表达式(CTE),都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/1238610.html