SQLServer使用T-SQL进阶之公用表表达式(CTE)(2)-木庄网络博客

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

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » SQLServer使用T-SQL进阶之公用表表达式(CTE)

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

2、递归公用表表达式(CTE)：

数据库系统概念第6版

相关推荐

评论