cte 学习总结

微软从SQl2005起引入了CTE(Common Table Expression)以强化T-SQL。这是一个类似于非持久视图的好东东。

cte语法：

[WITH <CTE_definition> [，...n]]
<SELECT， INSERT， UPDATE， or DELETE statement that
calls the CTEs>
<CTE_definition>::=
CTE_name [(column_name [，...n ])]
AS
(
CTE_query
)

按照MSDN介绍

1、公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

CTE 可用于：

创建递归查询。有关详细信息，请参阅使用公用表表达式的递归查询。
在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。
启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。
在同一语句中多次引用生成的表。
使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。

2、公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE 。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。当某个查询引用递归 CTE 时，它即被称为递归查询。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组件）中的数据。

递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中，递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。有关公用表表达式的详细信息，请参阅使用公用表表达式。

3、CTE递归原理:

递归CTE是由两个最小查询构建的.第一个是定位成员(Anchor Member,AM),它是一个非递归查询,第二个是递归成员(Recursive Member,RM),它是递归查询.在CTE括号中(AS 子句之后),定义独立查询或引用回相同CTE的查询,AM与RM由UNION ALL语句分隔.AM紧被调用一次,RM将被重复调用,直到查询不在返回数据行为止.可以使用UNION或UNION ALL运算符彼此追加多个AM,具体取决于是否希望删除重复数据(必须使用UNION ALL运算符来追加递归成员).语法如下:

--需要查询字段集合,也是查询结果的字段集合
WITH SimpleRecursive(filed names)
AS
(
    --查询定位成员表
    <SELECT Statement for the Anchor Member>
    UNION ALL
    --查询递归成员表
    <SELECT Statement for the Recursive Member>
)
SELECT * FROM SimpleRecursive

4、递归+排序：

WITH sf AS
(
SELECT a.jc,a.mc,cast(ROW_NUMBER()OVER(ORDER BY mc desc) AS varchar) AS LEVEL,* FROM a WHERE
NOT EXISTS (SELECT * FROM a as c WHERE c.k_sfpzjlh=a.k_sjjlh) --定位查找成员
UNION ALL
SELECT b.jc,b.mc,cast((LEVEL+'.'+cast(ROW_NUMBER()OVER(ORDER BY b.jc) AS varchar)) AS varchar) ,b.* FROM b INNER JOIN sf on b.k_sjjlh=sf.k_sfpzjlh
)

SELECT jc,mc,k_sfpzjlh,k_sjjlh,* FROM sf ORDER BY LEVEL

实例:我们将创建一个员工表和一个名为ReportsTo的自引用字段,其引用回Emloyee_ID,然后编写一个查询,其返回像Stephen(Employee_ID=2)报告的所有员工以及向Stephen的下属报告的所有员工.

代码清单:

--创建表
CREATE TABLE Employee_Tree
    (
      Employee_NM NVARCHAR(50) ,
      Employee_ID INT PRIMARY KEY ,
      ReportsTo INT
    )

INSERT  INTO Employee_Tree
VALUES  ( 'Richard', 1, NULL )
INSERT  INTO Employee_Tree
VALUES  ( 'Stephen', 2, 1 )
INSERT  INTO Employee_Tree
VALUES  ( 'Clemens', 3, 2 )
INSERT  INTO Employee_Tree
VALUES  ( 'Malek', 4, 2 )
INSERT  INTO Employee_Tree
VALUES  ( 'Goksin', 5, 4 )
INSERT  INTO Employee_Tree
VALUES  ( 'Kimberly', 6, 1 )
INSERT  INTO Employee_Tree
VALUES  ( 'Ramesh', 7, 5 )
--创建递归查询
WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo )
          AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo
               FROM     Employee_Tree
               WHERE    Employee_ID = 2
               UNION ALL
               SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo
               FROM     Employee_Tree p
                        INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo
             )
    SELECT  sr.Employee_ID AS empid ,
            sr.Employee_NM AS Emp ,
            et.Employee_NM AS Boss
    FROM    SimpleRecursive sr
            INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID

查询结果:

递归过程开始于Employee_ID=2的位置(定义成员或第一个SELECT).它获取该记录,并使用递归递归成员(UNION ALL之后的SELECT)获取所有向Stephen报告的记录以及该记录的子记录.(Goksin向Malek报告,Malek向Stephen报告).每一个后续递归都尝试找到更多的子记录.它们以先前递归已经找到的员工作为父记录.最终该递归不返回结果,这是导致递归停止的原因(为什么没有返回Kimberly的原因).如果该定位成员被改变成Employee_ID=1,则Kimberly也将在结果中被返回.

根据设计,递归成员将一直查找子记录,并可以无限循环.如果您怀疑将会进行许多循环,并希望限制递归调用次数,可以使用OPTION子句在外部查询的后面指定MAXRECURSION选项.

OPTION(MAXRECURSION 25)

这一选项将使sql server2005在cte出现指定限制时产生一条错误.默认情况下,限制为100(即省略该选项时).要不想指定选项.必须将MAXRECURSION设为0.

代码清单:

WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo )
          AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo
               FROM     Employee_Tree
               WHERE    Employee_ID = 2
               UNION ALL
               SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo
               FROM     Employee_Tree p
                        INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo
             )
    SELECT  sr.Employee_ID AS empid ,
            sr.Employee_NM AS Emp ,
            et.Employee_NM AS Boss
    FROM    SimpleRecursive sr
            INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID
            OPTION(MAXRECURSION 2)

结果如下:

还会看到以下错误:

消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 2。
一种可以避免出现此异常的方法是使用一个所产生的列来跟踪所在的级别,并包含在WHERE子句中,而不是MAXRECURSION.以下结果与上一示例相同,但不产生错误.

代码清单:

WITH    SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo ,Sublevel)
          AS ( SELECT   Employee_NM ,
                        Employee_ID ,
                        ReportsTo,
                        0
               FROM     Employee_Tree
               WHERE    Employee_ID = 2
               UNION ALL
               SELECT   p.Employee_NM ,
                        p.Employee_ID ,
                        p.ReportsTo,
                        Sublevel + 1
               FROM     Employee_Tree p
                        INNER JOIN SimpleRecursive A ON A.Employee_ID = p.ReportsTo
             )
    SELECT  sr.Employee_ID AS empid ,
            sr.Employee_NM AS Emp ,
            et.Employee_NM AS Boss
    FROM    SimpleRecursive sr
            INNER JOIN Employee_Tree et ON sr.ReportsTo = et.Employee_ID
            WHERE    Sublevel <=2