T-SQL查询进阶–详解公用表表达式(CTE)

by admin on 2019年9月24日

CTE 也叫公用表表达式和派生表非常类似 先定义一个USACusts的CTE  

 

在推出SQLServer2005之后,微软定义了一个新的查询架构叫做公共表表达式–CTE。CTE是一个基于简单查询的临时结果集,在一个简单的插入、更新、删除或者select语句的执行范围内使用。再本篇中,我们将看到如何定义和使用CTE。

一般情况下,我们用SELECT这些查询语句时,都是针对的一行记录而言,
如果要在查询分析器中对多行记录(即记录集)进行读取操作时,则需要使用到游标或WHILE等循环
奥门威尼斯网址 ,/
以下内容摘自

/
游标的类型:
  1、静态游标(不检测数据行的变化)
  2、动态游标(反映所有数据行的改变)
  3、仅向前游标(不支持滚动)
  4、键集游标(能反映修改,但不能准确反映插入、删除)

简介

简介


    
对于SELECT查询语句来说,通常情况下,为了使T-SQL代码更加简洁和可读,在一个查询中引用另外的结果集都是通过视图而不是子查询来进行分解的.但是,视图是作为系统对象存在数据库中,那对于结果集仅仅需要在存储过程或是用户自定义函数中使用一次的时候,使用视图就显得有些奢侈了.

   
公用表表达式(Common Table Expression)是SQL SERVER
2005版本之后引入的一个特性.CTE可以看作是一个临时的结果集,可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.

    
除此之外,根据微软对CTE好处的描述,可以归结为四点:

  •     
    可以定义递归公用表表达式(CTE)
  •     
    当不需要将结果集作为视图被多个地方引用时,CTE可以使其更加简洁
  •    
    GROUP BY语句可以直接作用于子查询所得的标量列
  •    
    可以在一个语句中多次引用公用表表达式(CTE)
WITH USACusts AS
(
  SELECT custid, companyname
  FROM Sales.Customers
  WHERE country = N'USA'
)
SELECT * FROM USACusts;

定义和使用CTE

通过使用CTE你能写和命名一个T-SQL select
语句,然后引用这个命名的语句就像使用一个表或者试图一样。

CTE下面就是定义一个CTE的语法:

WITH <expression_name> (Column1, Column2, …) AS (CTE Definition)

表示:

  • <expression_name>”   CTE的命名
  • “Column 1, Column2,…”  查询语句返回结果集的列名称
  • “CTE Definition”             select语句返回的结果集.

定义CTE需要跟着一个INSERT, UPDATE, DELETE,
或者SELECT的语句来引用CTE。假如CTE是一个批处理的一部分,那么语句之前用一个With开始然后以分号结束。当你定义了一个多重CTE,即一个CTE引用另一个CTE则需要被引用的CTE定义在引用的CTE之前。听起来可能有点混乱,那我们闲话少说看实例来说明吧。

下面是一些在CTE中可以被使用的选项:

  • ORDER BY (当使用top的时候可以使用)
  • INTO
  • OPTION (带有查询提示)
  • FOR XML
  • FOR BROWSE

游标使用顺序:
   1、定义游标
   2、打开游标
   3、使用游标
   4、关闭游标
   5、释放游标


公用表表达式(CTE)的定义


   
公用表达式的定义非常简单,只包含三部分:

  1.  
    公用表表达式的名字(在WITH之后)
  2.  
    所涉及的列名(可选)
  3.  
    一个SELECT语句(紧跟AS之后)

   
在MSDN中的原型:

1 WITH expression_name [ ( column_name [,...n] ) ] 
2 
3 AS 
4 
5 ( CTE_query_definition )

按照是否递归,可以将公用表(CTE)表达式分为递归公用表表达式和非递归公用表表达式.

with  ()  称为内部查询 
 与派生表相同,一旦外部查询完成后,CTE就自动释放了

递归CTE语句

我理解递归就是调用自己的过程。每一个递归处理的迭代都返回一个结果的子集。这个递归处理保持循环调用直至达到条件限制才停止。最终的结果集其实就是CTE循环中每一个调用超生的结果集的并集。

递归CTE,包含了至少两个查询定义,一个是select语句,另一个查询被作为“锚成员”,而其他的查询定义被作为循环成员。锚成员查询定义不包含CTE而循环成员中包括。另外,锚成员查询需要出现在CTE递归成员查询之前,且两者返回的列完全相同。可以有多个锚成员查询,其中每一个都需要与UNION
ALL, UNION, INTERSECT, 或者
EXCEPT联合使用。当然也有多重的递归查询定义,每一个递归查询定义一定与UNION
ALL联合使用。UNION ALL
操作符被用来连接最后的锚查询与第一个递归查询。接下来我们用实际立在来讨论一下CTE和递归CTE。

Transact-SQL:
declare 游标名 cursor [LOCAL | GLOBAL][FORWARD_ONLY |
SCROLL][STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS] 
  for selet语句   [for  update[of 列名[,列名]]
 注:LOCAL 局部游标     GLOBAL 全局游标
     FORWARD_ONLY 仅向前  SCROLL 滚动
     STATIC 静态  KEYSET 键集 DYNAMIC 动态
     READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行

    
对于SELECT查询语句来说,通常情况下,为了使T-SQL代码更加简洁和可读,在一个查询中引用另外的结果集都是通过视图而不是子查询来进行分解的.但是,视图是作为系统对象存在数据库中,那对于结果集仅仅需要在存储过程或是用户自定义函数中使用一次的时候,使用视图就显得有些奢侈了.

非递归公用表表达式(CTE)


  
非递归公用表表达式(CTE)是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE

  
非递归公用表表达式(CTE)的使用方式和视图以及子查询一致

  
比如一个简单的非递归公用表表达式:

奥门威尼斯网址 1

当然,公用表表达式的好处之一是可以在接下来一条语句中多次引用:

奥门威尼斯网址 2

前面我一直强调“在接下来的一条语句中”,意味着只能接下来一条使用:

奥门威尼斯网址 3

由于CTE只能在接下来一条语句中使用,因此,当需要接下来的一条语句中引用多个CTE时,可以定义多个,中间用逗号分隔:

奥门威尼斯网址 4

CTE内部方式 就是上面代码所表示的方式  其实还有一种外部方式

Example of a Simple CTE

如前所述,CTE
提供了一种能更好书写你的复杂代码的方法,提高了代码可读性。如下面的复杂的代码

USE AdventureWorks2012;
GO
SELECT YearMonth, ProductID, SumLineTotal FROM
( SELECT CONVERT(CHAR(7),ModifiedDate,120) AS YearMonth , ProductID , SUM(LineTotal) AS SumLineTotal FROM Sales.SalesOrderDetail
GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120) )
MonthlyProductSales WHERE YearMonth = ‘2008-06’;

 

代码是一个select语句,有一个子查询在FROM后面的子句中。子查询被当做一个派生表
MonthlyProductSales,查询表按照根据ModifiedDate的月和年粒度进行汇总,将LineTotal
金额加在一起。在筛选出年和月份为“2008-06”**
的结果后进行分组汇总。

接下来我们用CTE来实现上述的代码。

USE AdventureWorks2012;
GO
— CTE 定义
WITH MonthlyProductSales AS (
SELECT CONVERT(CHAR(7),ModifiedDate,120) AS YearMonth , ProductID , SUM(LineTotal) AS SumLineTotal FROM Sales.SalesOrderDetail GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120) )
— 包含CTE的select语句
SELECT * FROM MonthlyProductSales WHERE YearMonth = ‘2008-06’;

在这个代码中,我将衍生表子查询放到了CTE命名为MonthlyProductSales
的里面,然后取代了子查询,在我的Select语句中调用CTE命名的表MonthlyProductSales,这样是不是显得更加容易理解和维护了?

获取游标的数据
  FETCH [[NEXT | PRIOR | FIRST | LAST |
  ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
  From ] 游标名 [into 变量]
  注:
    NEXT  下一行  PRIOR  上一行  FIRST 第一行
    LAST  最后一行  ABSOLUTE n 第n行
    RELATIVE n 当前位置开始的第n行
    into 变量  把当前行的各字段值赋值给变量

    公用表表达式(Common Table Expression)是SQL SERVER
2005版本之后引入的一个特性.CTE可以看作是一个临时的结果集,可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.

递归公用表表达式(CTE)


   
递归公用表表达式很像派生表(Derived Tables
),指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是,CTE可以在一次定义多次进行派生递归.对于递归的概念,是指一个函数或是过程直接或者间接的调用其自身,递归的简单概念图如下:

奥门威尼斯网址 5

递归在C语言中实现的一个典型例子是斐波那契数列:

1 long fib(int n)   
2 {   
3      if (n==0) return 0;
4    if (n==1) return 1;   
5      if (n>1) return fib(n-1)+fib(n-2);
6 } 

 
 上面C语言代码可以看到,要构成递归函数,需要两部分。第一部分是基础部分,返回固定值,也就是告诉程序何时开始递归。第二部分是循环部分,是函数或过程直接或者间接调用自身进行递归.

 

  
对于递归公用表达式来说,实现原理也是相同的,同样需要在语句中定义两部分:

  •   
    基本语句
  •   
    递归语句

  
在SQL这两部分通过UNION ALL连接结果集进行返回:

  
比如:在AdventureWork中,我想知道每个员工所处的层级,0是最高级

奥门威尼斯网址 6

 

 
 这么复杂的查询通过递归CTE变得如此优雅和简洁.这也是CTE最强大的地方.

  
当然,越强大的力量,就需要被约束.如果使用不当的话,递归CTE可能会出现无限递归。从而大量消耗SQL
Server的服务器资源.因此,SQL
Server提供了OPTION选项,可以设定最大的递归次数:

  
还是上面那个语句,限制了递归次数:

奥门威尼斯网址 7

所提示的消息:

奥门威尼斯网址 8

 这个最大递归次数往往是根据数据所代表的具体业务相关的,比如这里,假设公司层级最多只有2层.

 

WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

C(orderyear, custid)  可以理解为 select orderyear, custid from C   指定返回你想要的列  不过个人感觉没什么用!

它和派生表相同 也可以在CTE中查询使用参数

DECLARE @empid AS INT = 3;

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
  WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

使用多重CTE的例子

 

假如你的代码更加复杂并且包含多个子查询,你就得考虑重写来简化维护和提高易读性。重写的方式之一就是讲子查询重写成CTEs。为了更好地展示,先看一下下面的非CTE复杂查询如下:

USE AdventureWorks2012;
GO
SELECT
SalesPersonID ,
SalesYear ,
TotalSales ,
SalesQuotaYear ,
SalesQuota FROM
( –第一个子查询
SELECT
SalesPersonID ,
SUM(TotalDue) AS TotalSales ,
YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, YEAR(OrderDate) ) AS Sales
JOIN ( — 第二个子查询
SELECT
BusinessEntityID ,
SUM(SalesQuota)AS SalesQuota ,
YEAR(QuotaDate) AS SalesQuotaYear
FROM Sales.SalesPersonQuotaHistory
GROUP BY BusinessEntityID, YEAR(QuotaDate) )
AS Sales_Quota ON Sales_Quota.BusinessEntityID = Sales.SalesPersonID
AND Sales_Quota.SalesQuotaYear = Sales.SalesYear
ORDER BY SalesPersonID, SalesYear;

我直接上代码啊,看看如何通过CTE来简化这个代码。

USE AdventureWorks2012;
GO
WITH
— 第一个被CTE重写的子查询
WITH Sales AS (
SELECT
SalesPersonID ,
SUM(TotalDue) AS TotalSales ,
YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, YEAR(OrderDate) ),
— 第二个被CTE重写的子查询
Sales_Quota AS (
SELECT
BusinessEntityID ,
SUM(SalesQuota)AS SalesQuota ,
YEAR(QuotaDate) AS SalesQuotaYear
FROM Sales.SalesPersonQuotaHistory
GROUP BY BusinessEntityID, YEAR(QuotaDate) )
— SELECT 使用多重CTEs
SELECT
SalesPersonID ,
SalesYear ,
TotalSales ,
SalesQuotaYear ,
SalesQuota
FROM Sales
JOIN Sales_Quota
ON Sales_Quota.BusinessEntityID = Sales.SalesPersonID
AND Sales_Quota.SalesQuotaYear = Sales.SalesYear
ORDER BY SalesPersonID, SalesYear;

 

着这段代码中,我将两个子查询转移到两个不同的CTEs中,第一个CTE用Sales来命名,定义了的第二个子查询,叫做SalesQuota在第一个CTE后面用逗号分隔与第二个。定义完成后,引用这两个别名来实现最终的select
语句,结果与之前复杂的代码结果完全相同。.

能够用一个单一的WITH
子句定义一个多重CTEs,然后包括这些CTEs在我的最中的TSQL语句中,这使得我可以更容易的读、开发和调试。使用多重CTEs对于复杂的TSQL逻辑而言,让我们将代码放到更容易管理的细小部分里面分隔管理。

游标状态变量:
    @@fetch_status  游标状态
         0 成功  -1 失败  -2 丢失
    @@cursor_rows 游标中结果集中的行数
        n 行数 -1 游标是动态的  0 空集游标
操作游标的当前行:
   current of 游标名

     除此之外,根据微软对CTE好处的描述,可以归结为四点:

总结 


   
CTE是一种十分优雅的存在。CTE所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归CTE可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。

 

本文来自:

定义多个CTE

CTE引用CTE

为了实现CTE引用另一个CTE我们需要满足下面两个条件:

  1. 被定义在同一个WITH自居中作为CTE被引用
  2. 被定义在被引用的CTE后面

代码如下:

USE AdventureWorks2012; GO WITH
–第一个被重写的子查询CTE Sales AS ( SELECT SalesPersonID ,
SUM(TotalDue) AS TotalSales , YEAR(OrderDate) AS SalesYear FROM
Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY
SalesPersonID, YEAR(OrderDate) ), —
第二个子查询引用第一个CTETotalSales AS ( SELECT
SUM(TotalSales)AS TotalSales , SalesYear FROM Sales GROUP BY
SalesYear ) — 调用第二个CTE SELECT * FROM TotalSales ORDER
BY SalesYear;

 

这个代码中,我定义了一个CTE命名为Sales
,被第二个CTE引用,定义第二个CTE叫做TotalSales,在这个CTE 
中我汇总了TotalSales
列,通过组合SalesYear列。最后我使用Select语句引用第二个CTE。

以下例子,在SQL SERVER 2000 测试成功

  •      可以定义递归公用表表达式(CTE)
  •      当不需要将结果集作为视图被多个地方引用时,CTE可以使其更加简洁
  •     GROUP BY语句可以直接作用于子查询所得的标量列
  •     可以在一个语句中多次引用公用表表达式(CTE)
WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

CTE递归调用CTE实例

另一个CTE的主要功能就是调用自己,当用CTE调用自己的时候,就行程了CTE递归调用。一个递归CTE有两个主要部分,一个是锚成员,一个是递归成员。锚成员开启递归成员,这里你可以把锚成员查询当做一个没有引用CTE的查询。而递归成员将会引用CTE。这个锚成员确定了初始的记录集,然后递归成员来使用这个初始记录集。为了更好地理解递归CTE,我将创建一个实例数据通过使用递归CTE,

下面就是代码Listing 6:

 

USE tempdb; GO —
先创建一个用户表 CREATE TABLE dbo.Employee ( EmpID smallint NOT NULL,
EmpName nvarchar(100) NOT NULL, Position nvarchar(50) NOT NULL, MgrId
int NULL ); — 插入数据INSERT INTO dbo.Employee VALUES (1,
N’Joe Steel’, N’President’,NULL) ,(2, N’John Smith’, N’VP Western Region
Sales’,1) ,(3, N’Sue Jones’, N’VP Easter Region’,1) ,(4, N’Lynn
Holland’, N’Sales Person’,2) ,(5, N’Linda Thomas’, N’Sales Person’,3 )
,(6, N’Kathy Johnson’, N’Admin Assistant’,1) ,(7, N’Rich Little’,
N’Sales Person’,3) ,(8, N’David Nelson’, N’Sales Person’, 2) ,(9, N’Mary
Jackson’, N’Sales Person’, 3);

Listing 6

在Listing
6我创建了一个员工表,包含了员工信息,这个表中插入了9个不同的员工,MgrId
字段用来区分员工的领导的ID,这里有一个字段为null的记录。这个人没有领导且是这里的最高级领导。来看看我将如何使用递归CTE吧,在Listing7中:

USE tempdb;
GO
WITH ReportingStructure(MgrID, EmpID, EmpName, Position, OrgLevel) AS 
(
    --锚部分
    SELECT MgrID, EmpID, EmpName, Position, 0 AS OrgLevel
    FROM dbo.Employee 
    WHERE MgrID IS NULL
    UNION ALL
    -- 递归部分
    SELECT e.MgrID, e.EmpID, e.EmpName
         , e.Position, r.OrgLevel + 1
    FROM dbo.Employee AS e
    INNER JOIN ReportingStructure AS r
    ON e.MgrID = r.EmpID 
)
SELECT MgrID, EmpID, EmpName, Position, OrgLevel 
FROM ReportingStructure;

Listing 7

执行脚本结果:

MgrID EmpID  EmpName        Position                   OrgLevel
----- ------ -------------- -------------------------- -----------
NULL  1      Joe Steel      President                  0
1     2      John Smith     VP Western Region Sales    1
1     3      Sue Jones      VP Easter Region           1
1     6      Kathy Johnson  Admin Assistant            1
2     4      Lynn Holland   Sales Person               2
2     8      David Nelson   Sales Person               2
3     5      Linda Thomas   Sales Person               2
3     7      Rich Little    Sales Person               2
3     9      Mary Jackson   Sales Person               2

我们能发现这个结果是所有员工分级结构,注意OrgLevel
字段确定了分层等级结构,当你看到0的时候说明这个人就是最大的领导了,每一个员工过的直属领导都比自己的OrgLevel
大1。

use pubs
go

 

多个CTE用 , 隔开 通过with 内存 可以在外查询中多次引用

控制递归

有时候会出现无穷递归的CTE的可能,但是SQLServer有一个默认的最大递归值来避免出现无限循环的CTE递归。默认是100,下面我来举例说明:

USE tempdb; GO WITH
InfiniteLoopCTE as ( — Anchor Part SELECT EmpID, MgrID, Position FROM
dbo.Employee WHERE MgrID = 1 UNION ALL — Recursive Part SELECT
InfiniteLoopCTE.EmpID , InfiniteLoopCTE.MgrID , InfiniteLoopCTE.Position
FROM InfiniteLoopCTE JOIN dbo.Employee AS e ON e.EmpID =
InfiniteLoopCTE.MgrID ) SELECT * FROM InfiniteLoopCTE;

Listing 8

这部分代码引发了一个无限循环,因为递归部分将永远返回多行数据,这部分查询返回的结果是MrgID
为1的结果。而我去运行这段代码后,只循环了100次,这就是由于最大递归次数的默认为100。当然这个值也是可以设定的。假如我们打算超过100次,150次的话,如下所示:

USE tempdb; GO –Creates an
infinite loop WITH InfiniteLoopCTE as ( — 锚部分 SELECT EmpID, MgrID, Position FROM
dbo.Employee WHERE MgrID = 1 UNION ALL — 递归部分 SELECT InfiniteLoopCTE.EmpID ,
InfiniteLoopCTE.MgrID , InfiniteLoopCTE.Position FROM InfiniteLoopCTE
JOIN dbo.Employee AS e ON e.EmpID = InfiniteLoopCTE.MgrID ) SELECT *
FROM InfiniteLoopCTE OPTION (MAXRECURSION 150);

Listing 9

通过设定MAXRECUSION
的值为150次实现了递归150次的最大递归限制,这个属性的最大值为32,767。

declare @auid char(12),@aulname varchar(20),@aufname varchar(20), @st char(2),@auinfo varchar(50)
declare auth_cur cursor for
select au_id, au_lname, au_fname, state
from authors

公用表表达式(CTE)的定义

WITH YearlyCount AS
(
  SELECT YEAR(orderdate) AS orderyear,
    COUNT(DISTINCT custid) AS numcusts
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear, 
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
  LEFT OUTER JOIN YearlyCount AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;

何时使用CTE

当然我们学习了如何使用CTE就要知道什么时候来使用它,下面三种情况是使用CTE简化你的T-SQL语句的情况:

  1. 查询中需要递归
  2. 查询中有多个子查询,或者你有重复的相同的子查询在单一语句中。
  3. 查询时复杂庞大的

open auth_cur


可以需要在多个相同表结果做物理实例化  这样可以节省很多查询时间
或者在临时表和表变量中固化内部查询结果

总结

CTE的功能为SQLServer
提供了强大的补充,它让我们可以将复杂的代码切成很多易于管理和读取的小的代码段,同时还允许我们使用它来建立递归代码。CTE提供了另一种方法来实现复杂的T-SQL逻辑,为将来我们的开发提供了非常好的代码规范和易读性,

fetch next from auth_cur into @auid,@aulname,@aufname, @st
while (@@fetch_status=0)
  begin
    print ‘作者编号: ‘+@auid
    print ‘作者姓名: ‘+@aulname+’,’+@aufname
    print ‘所在州: ‘+@st
    print ‘————————–‘
    fetch next from auth_cur into @auid,@aulname,@aufname, @st
  end

    公用表达式的定义非常简单,只包含三部分:

递归CTE

close auth_cur
deallocate auth_cur

  1.   公用表表达式的名字(在WITH之后)
  2.   所涉及的列名(可选)
  3.   一个SELECT语句(紧跟AS之后)

递归CTE至少由两个查询定义,至少一个查询作为定位点成员,一个查询作为递归成员。

Transact-SQL 游标主要用于存储过程、触发器和Transact-SQL
脚本中,它们使结果集的内容可用于其他Transact-SQL 语句。
/*另一个例子
来自:
原例子是用于说明如何用WHILE代替游标及其好处,这里只摘出使用游标的例子
*/
  DECLARE @tbTargetPermissions    table(TargetPermissionId uniqueidentifier NOT NULL PRIMARY KEY)
奥门威尼斯网址 9    INSERT INTO @tbTargetPermissions 
奥门威尼斯网址 10        SELECT [TargetPermissionId] 
奥门威尼斯网址 11        FROM [ps_RolePermissions] 
奥门威尼斯网址 12        WHERE [TargetId] = @TargetId AND [RoleId] = @RoleId
奥门威尼斯网址 13    
奥门威尼斯网址 14    DECLARE @TargetPermissionId uniqueidentifier;
奥门威尼斯网址 15
奥门威尼斯网址 16    –定义游标
奥门威尼斯网址 17    DECLARE TargetPermissions_ByRoleId_Cursor CURSOR FOR 
奥门威尼斯网址 18        SELECT [TargetPermissionId] FROM @tbTargetPermissions 
奥门威尼斯网址 19
奥门威尼斯网址 20    –打开游标
奥门威尼斯网址 21    OPEN TargetPermissions_ByRoleId_Cursor
奥门威尼斯网址 22
奥门威尼斯网址 23    –读取游标第一条记录
奥门威尼斯网址 24    FETCH NEXT FROM    TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId
奥门威尼斯网址 25
奥门威尼斯网址 26    –检查@@FETCH_STATUS的值,以便进行循环读取
奥门威尼斯网址 27    WHILE @@FETCH_STATUS = 0
奥门威尼斯网址 28    BEGIN
奥门威尼斯网址 29        EXEC ps_TargetPermissionEntity_Select @TargetPermissionId;
奥门威尼斯网址 30
奥门威尼斯网址 31        FETCH NEXT FROM    TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId;
奥门威尼斯网址 32    END
奥门威尼斯网址 33
奥门威尼斯网址 34    –关闭游标
奥门威尼斯网址 35    CLOSE TargetPermissions_ByRoleId_Cursor
奥门威尼斯网址 36    DEALLOCATE TargetPermissions_ByRoleId_Cursor

    在MSDN中的原型:

递归成员是一个引用CTE名称的查询
,在第一次调用递归成员,上一个结果集是由上一次递归成员调用返回的。
其实就和C# 方法写递归一样  返回上一个结果集 依次输出

<h3>
   心静似高山流水不动,心清若巫峰雾气不沾。
</h3>

WITH expression_name [ ( column_name [,...n] ) ] 

AS 

( CTE_query_definition ) 
   WITH    Emp
 AS ( SELECT  * FROM  dbo.dt_users
               WHERE  id=2
                UNION ALL  
                SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
             )
    SELECT *
     FROM Emp 

 

在前面也写过 sql 语句的执行顺序 其实到  FROM Emp   时
就进行了节点第一次递归  当我们递归到第三次的时候 这个为执行的sql
语句实际是什么样的呢

  
按照是否递归,可以将公用表(CTE)表达式分为递归公用表表达式和非递归公用表表达式.

   WITH    Emp
 AS ( SELECT  * FROM  dbo.dt_users
               WHERE  id=2
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=3
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=4
                UNION ALL  
                SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
             )
    SELECT *
     FROM Emp 

 

简单理解可以把它看成两部分

非递归公用表表达式(CTE)

SELECT  * FROM  dbo.dt_users
               WHERE  id=2

   SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id

上部分的结果集 会储存成最后显示的结果 下部分的结果集  就是下一次递归的
上部分结果集 依次拼接  就是这个递归最后的结果集 

  
非递归公用表表达式(CTE)是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE

下部分 在详解  认真看很有意思

  
非递归公用表表达式(CTE)的使用方式和视图以及子查询一致

  SELECT d.* FROM  Emp

SELECT d.* FROM   dbo.dt_users d

   比如一个简单的非递归公用表表达式:

from Emp 源数据来自  d  在 on  d.agent_id = Emp.id 就是自连接 而 Emp.id
结果 来自哪里呢  就是上部分结果集
如果是第一次运行结果集就是上部分运行的结果 
 记住下部分操作结果集都是当前的上部分结果集。

  
奥门威尼斯网址 37

默认情况下递归是100次 也可在 外部查询 指定递归次数 MAXRECURSION N 0~32767 次范围 MAXRECURSION 0 并不是0次实际上是递归次数无限制

 

 

   当然,公用表表达式的好处之一是可以在接下来一条语句中多次引用:

 

  
奥门威尼斯网址 38

 

   前面我一直强调“在接下来的一条语句中”,意味着只能接下来一条使用:

  
奥门威尼斯网址 39

 

  
由于CTE只能在接下来一条语句中使用,因此,当需要接下来的一条语句中引用多个CTE时,可以定义多个,中间用逗号分隔:

  
奥门威尼斯网址 40

 

递归公用表表达式(CTE)


    递归公用表表达式很像派生表(Derived Tables
),指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是,CTE可以在一次定义多次进行派生递归.对于递归的概念,是指一个函数或是过程直接或者间接的调用其自身,递归的简单概念图如下:

  
奥门威尼斯网址 41

    递归在C语言中实现的一个典型例子是斐波那契数列:

long fib(int n)   
{   
     if (n==0) return 0;
   if (n==1) return 1;   
     if (n>1) return fib(n-1)+fib(n-2);
} 

  

  
上面C语言代码可以看到,要构成递归函数,需要两部分。第一部分是基础部分,返回固定值,也就是告诉程序何时开始递归。第二部分是循环部分,是函数或过程直接或者间接调用自身进行递归.

 

  
对于递归公用表达式来说,实现原理也是相同的,同样需要在语句中定义两部分:

  •    基本语句
  •    递归语句

   在SQL这两部分通过UNION ALL连接结果集进行返回:

   比如:在AdventureWork中,我想知道每个员工所处的层级,0是最高级

  
奥门威尼斯网址 42

  

   这么复杂的查询通过递归CTE变得如此优雅和简洁.这也是CTE最强大的地方.

  
当然,越强大的力量,就需要被约束.如果使用不当的话,递归CTE可能会出现无限递归。从而大量消耗SQL
Server的服务器资源.因此,SQL
Server提供了OPTION选项,可以设定最大的递归次数:

   还是上面那个语句,限制了递归次数:

  
奥门威尼斯网址 43

   所提示的消息:

  
奥门威尼斯网址 44

 

  
这个最大递归次数往往是根据数据所代表的具体业务相关的,比如这里,假设公司层级最多只有2层.

 

总结 


   
CTE是一种十分优雅的存在。CTE所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归CTE可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图