SQLServer如何使用T-SQL进阶之公用表表达式(CTE)

有一条长长的石头,外形像一条龙,石头上长满斑斑点点的东西,像龙身上的鳞。人生就是一场旅行,不在乎目的地,在乎的应该是沿途的风景以及看风景的心情。春色满园关不住,一枝红杏出墙来。

在编写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)的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。