T-SQL公用表表达式有何作用

发布时间：2024-01-01 15:45:36 所属栏目：MsSql教程来源：DaWei

导读： 这篇文章给大家分享的是“T-SQL公用表表达式有何作用及好处，怎么用”，对大家学习和理解有一定的参考价值和帮助，有这方面学习需要的朋友，接下来就跟随小编一起学习一下吧。

这篇文章给大家分享的是“T-SQL公用表表达式有何作用及好处，怎么用”，对大家学习和理解有一定的参考价值和帮助，有这方面学习需要的朋友，接下来就跟随小编一起学习一下吧。

在编写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)：

对于递归公用表达式来说，只需要在语句中定义两部分：
基本语句
递归语句

先建一张表栏目表如下，栏目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可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。

关于“T-SQL公用表表达式有何作用及好处，怎么用”就介绍到这了，如果大家觉得不错可以参考了解看看，如果想要了解更多，小编每天都会为大家更新不同的知识。

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!