SQL Server 2008中的CTE递归查询[MSSQL防范]

个人认为sql server的递归查询相关于Oracle中的递归查询越发难以理解.从SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,这对查询树形或层次构造的数据很有效.CTE即公用表表达式,固然不得当,但你可以将它看做成一个暂时命名的后果调集.
我们先成立一个示例表,名称为MENU,表示菜单的层次构造:

CREATE TABLE MENU
 (
    name nvarchar(50) NOT NULL PRIMARY KEY,
    senior nvarchar(50) NULL
);
 
 INSERT INTO MENU values
    ('文件',NULL),
    ('新建','文件'),
    ('项目','新建'),
    ('利用当前衔接查询','新建');

表示的菜单层次以下:

文件
    新建
        项目
        利用当前衔接查询

OK,先看CTE的语法:
  WITH CTE名称(字段列表)
  AS
  (
    查询语句
  )
比方:
WITH lmenu(name,senior)
as
(
    SELECT name,senior from menu
)

我们定义了一个名称为lmenu的CTE,这样我们后续便可直接利用lmenu来查询,如:
SELECT * FROM lmenu

假如我们在定义CTE的查询语句中直接引用CTE表本身,则会形成递归查询,当然递归查询具有自己的特别构造,下面的SQL通过递归查询获得每个菜单的层次深度:

WITH lmenu(name,senior,level) as
 (
    SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL
    UNION ALL
    SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
    where a.senior = b.name
)

SELECT *  from lmenu

后果:

name                                              senior                                            level
-------------------------------------------------- -------------------------------------------------- -----------
文件                                                NULL                                              0
新建                                                文件                                                1
利用当前衔接查询                              新建                                                2
项目                                                新建                                                2

注意查询定义语句,它由两条查询语句构成,此中

SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL

称为定位成员,SQL Server通过此语句来判断能否持续举行递归.
语句

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
    where a.senior = b.name

称之为递归成员,其特点为from子句中引用了CTE对象自身.
递归CTE具有一些限制条件(引自MSDN):

至少有一个定位点成员和一个递归成员,当然,你可以定义多个定位点成员和递归成员,但全部定位点成员必须在递归成员的前面
定位点成员之间必须利用UNION ALL、UNION、INTERSECT、EXCEPT调集运算符,最后一个定位点成员与递归成员之间必须利用UNION ALL,递归成员之间也必须利用UNION ALL衔接
定位点成员和递归成员中的字段数目和范例必须完好一致
递归成员的FROM子句只能引用一次CTE对象
递归成员中不答应呈现下列项
    SELECT DISTINCT
    GROUP BY
    HAVING
    标量聚合
    TOP
    LEFT、RIGHT、OUTER JOIN（答应呈现 INNER JOIN）
    子查询

CTE递归查询的履行方法:
递归的终止依靠于定位点成员的,假如理解了这一点,也就理解了递归查询的履行方法.
我们来看上例的履行履行历程:

SELECT * FROM lmenu

这条语句进入递归查询

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
    where a.senior = b.name

作为最外层的语句,明显递归的第一层应当按照MENU表的记录来循环(假如查询履行筹划,这表示一个嵌套循环),假定menu表中查询出的记录次序以下:

name                                              senior
-------------------------------------------------- --------------------------------------------------
文件                                                NULL
新建                                                文件
利用当前衔接查询                              新建
项目                                                新建

第一条记录:
首先判断能否进入递归,由于 文件包含在定位点成员后果集合,不符合递归条件,所以不进入递归,直接返回从定位点成员调集合返回记录:

select name,senior,0 level from menu where senior is null and name='文件'

name                                              senior                                            level
-------------------------------------------------- -------------------------------------------------- -----------
文件                                                NULL                                              0

第二条记录:
即NAME = '新建', 定位点成员后果集合没有该记录,将进入递归:
将当前行的值带入递归成员:

SELECT A.NAME,A.SENIOR,b.level+1 level FROM MENU A,lmenu b
    where a.senior = b.name
        AND a.senior = '文件'
        AND a.name='新建'