【原】SQL Server中使用CTE递归查询

背景

问题

思路

CTE

CTE递归查询

结束语

参考资料

背景

好久未写博了，最近遇到一个问题：“怎么遍历出一个父级菜单下所有子菜单？”小的随后用CTE递归查询解决此问题，整理记录下来以作分享。

问题

怎么遍历出一个父级菜单下所有子菜单?

思路

用CTE递归查询解决
写一个自定义函数/存储过程迭代算法来实现

CTE

定义
语法结构
使用CTE准则
示例代码

定义

CTE（Common Table Expressions）是从SQL Server 2005以后版本才有的。指定的临时命名结果集，这些结果集称为CTE。与派生表类似，不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。使用CTE能改善代码可读性，且不损害其性能。

语法结构

CTE 的基本语法结构如下：

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

AS

( CTE_query_definition )

只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。

运行 CTE 的语句为：

SELECT <column_list>

FROM expression_name;

使用CTE准则

创建CTE时，需要注意的一些准则，详见MSND列出的使用准则：

定义CTE时最好加前缀”;”
CTE内部定义的列字段要保持一致
CTE with之后第一句必须使用CTE的select。即CTE的生命周期只是在第一次使用之后就消亡。
sp中只能使用一次with语句。
定义多个CTE时，只声明一个with关键字就行，比如

with test1

as

(

select * …………

)，

test2 as

(

select * …………

)

通用表表达式(CTE)是SQL Server的一项新功能。本质上CTE是一个临时结果集，它仅仅存在于它发生的语句中。可以在SELECT、INSERT、DELETE或CREATE VIEW语句中建立一个CTE

示例代码

创建测试表

USE MASTER
GO
 
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='NODE')
DROP TABLE NODE
---创建表
CREATE TABLE NODE
(
   NID INT PRIMARY KEY,
   PARENTID INT NOT NULL,
   NAME  NVARCHAR(50) NOT NULL
)

插入数据

INSERT INTO NODE VALUES
    (1,0,'aa'),
    (2,1,'bb'),
    (3,2,'cc'),
    (4,2,'dd'),
    (5,3,'ee'),
    (6,4,'ff'),
    (7,5,'gg'),
    (8,4,'hh'),
    (9,3,'ii'),
    (10,5,'jj');

创建一个CTE的DEMO

;WITH NodeCTE(NID,PARENTID,NAME)
AS
(
SELECT NID,PARENTID,NAME FROM NODE WHERE NAME='BB'
UNION ALL
SELECT B.NID,B.PARENTID,B.NAME FROM NodeCTE A ,NODE B WHERE B.PARENTID=A.NID
)

SELECT * FROM NodeCTE;

CTE递归查询

CTE递归查询结构
CTE递归查询原理
原节点图和执行CTE结果集

CTE递归查询结构（引自MSDN）

Transact-SQL 中的递归 CTE 的结构与其他编程语言中的递归例程相似。尽管其他语言中的递归例程返回标量值，但递归 CTE 可以返回多行。

递归 CTE 由下列三个元素组成：

例程的调用。

递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。

CTE_query_definitions 被视为定位点成员，除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前，而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。

例程的递归调用。

递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。

终止检查。

终止检查是隐式的；当上一个调用中未返回行时，递归将停止。

注意

如果递归 CTE 组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。在测试递归查询的结果时，可以通过在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之间的值，来限制特定语句允许的递归级数。