[转载]SQL Server 2008中的CTE递归查询

[转载]SQL Server 2008中的CTE递归查询 – xfrog – 博客园.

今天基本搞清楚SQL Server中递归查询的实现方式,So,先记录下来。不过呢,个人觉得SQL Server的递归查询相对于Oracle中的递归查询更加难以理解困惑
从SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,这对查询树形或层次结构的数据很有用。CTE即公用表表达式,虽然不恰当,但你可以将它看做成一个临时命名的结果集合。
我们先建立一个示例表,名称为MENU,表示菜单的层次结构:

显示行号 复制代码 SQL
  1. CREATE TABLE MENU 
  2.  (
  3.     name nvarchar(50) NOT NULL PRIMARY KEY,
  4.     senior nvarchar(50) NULL
  5. );
  6.  
  7.  INSERT INTO MENU values
  8.     ('文件',NULL),
  9.     ('新建','文件'),
  10.     ('项目','新建'),
  11.     ('使用当前连接查询','新建');
.src_container { background-color: rgb(231, 229, 220); width: 99%; overflow: hidden; margin: 12px 0pt ! important; padding: 0px 3px 3px 0px; }.src_container .titlebar { background-color: rgb(212, 223, 255); border-width: 1px 1px 0pt; border-style: solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; padding: 3px 24px; margin: 0pt; width: auto; line-height: 120%; overflow: hidden; text-align: left; font-size: 12px; }.src_container .toolbar { display: inline; font-weight: normal; font-size: 100%; float: right; color: rgb(0, 0, 255); text-align: left; overflow: hidden; }.toolbar span.button { display: inline; font-weight: normal; font-size: 100%; color: rgb(0, 0, 255); text-align: left; overflow: hidden; cursor: pointer; }.src_container div.clientarea { background-color: white; border: 1px solid rgb(79, 129, 189); margin: 0pt; width: auto ! important; height: auto; overflow: auto; text-align: left; font-size: 12px; font-family: “Courier New”,”Consolas”,”Fixedsys”,courier,monospace,serif; }.src_container ol.mainarea { padding: 0pt 0pt 0pt 52px; margin: 0pt; background-color: rgb(247, 247, 255) ! important; }.number_show { padding-left: 52px ! important; list-style: decimal outside none ! important; }.number_show li { list-style: decimal outside none ! important; border-left: 1px dotted rgb(79, 129, 189); }.number_hide { padding-left: 0px ! important; list-style-type: none ! important; }.number_hide li { list-style-type: none ! important; border-left: 0px none; }ol.mainarea li { display: list-item ! important; font-size: 12px ! important; margin: 0pt ! important; line-height: 18px ! important; padding: 0pt 0pt 0pt 0px ! important; background-color: rgb(247, 247, 255) ! important; color: rgb(79, 129, 189); }ol.mainarea li pre { color: black; line-height: 18px; padding: 0pt 0pt 0pt 12px ! important; margin: 0em; background-color: rgb(255, 255, 255) ! important; }.linewrap ol.mainarea li pre { white-space: pre-wrap; word-wrap: break-word; }ol.mainarea li pre.alt { background-color: rgb(247, 247, 255) ! important; }表示的菜单层次如下:

文件
新建
项目
使用当前连接查询

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

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

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

显示行号 复制代码 SQL
  1. WITH lmenu(name,senior,level) as
  2.  (
  3.     SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL
  4.     UNION ALL
  5.     SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
  6.     where a.senior = b.name
  7. )
  8. SELECT *  from lmenu

.src_container { background-color: rgb(231, 229, 220); width: 99%; overflow: hidden; margin: 12px 0pt ! important; padding: 0px 3px 3px 0px; }.src_container .titlebar { background-color: rgb(212, 223, 255); border-width: 1px 1px 0pt; border-style: solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; padding: 3px 24px; margin: 0pt; width: auto; line-height: 120%; overflow: hidden; text-align: left; font-size: 12px; }.src_container .toolbar { display: inline; font-weight: normal; font-size: 100%; float: right; color: rgb(0, 0, 255); text-align: left; overflow: hidden; }.toolbar span.button { display: inline; font-weight: normal; font-size: 100%; color: rgb(0, 0, 255); text-align: left; overflow: hidden; cursor: pointer; }.src_container div.clientarea { background-color: white; border: 1px solid rgb(79, 129, 189); margin: 0pt; width: auto ! important; height: auto; overflow: auto; text-align: left; font-size: 12px; font-family: “Courier New”,”Consolas”,”Fixedsys”,courier,monospace,serif; }.src_container ol.mainarea { padding: 0pt 0pt 0pt 52px; margin: 0pt; background-color: rgb(247, 247, 255) ! important; }.number_show { padding-left: 52px ! important; list-style: decimal outside none ! important; }.number_show li { list-style: decimal outside none ! important; border-left: 1px dotted rgb(79, 129, 189); }.number_hide { padding-left: 0px ! important; list-style-type: none ! important; }.number_hide li { list-style-type: none ! important; border-left: 0px none; }ol.mainarea li { display: list-item ! important; font-size: 12px ! important; margin: 0pt ! important; line-height: 18px ! important; padding: 0pt 0pt 0pt 0px ! important; background-color: rgb(247, 247, 255) ! important; color: rgb(79, 129, 189); }ol.mainarea li pre { color: black; line-height: 18px; padding: 0pt 0pt 0pt 12px ! important; margin: 0em; background-color: rgb(255, 255, 255) ! important; }.linewrap ol.mainarea li pre { white-space: pre-wrap; word-wrap: break-word; }ol.mainarea li pre.alt { background-color: rgb(247, 247, 255) ! important; }结果:

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=’新建’

由于递归的关联条件是a.senior = b.name,所以b.name=’文件’,以此条件进入下级递归,这实际上就是第一条记录的情况,由于name=’文件’符合定位点条件,所以将终止递 归,如果我们用子查询来替换掉lmenu递归成员,第二条记录的查询语句实际为:

显示行号 复制代码 SQL
  1. SELECT a.name,a.senior,b.level+1 from menu a, (
  2.   select name,senior,0 level  from menu where senior is null and name='文件'
  3. ) b
  4. where a.senior=b.name
  5.     and a.senior = '文件'
  6.     and a.name='新建'

.src_container { background-color: rgb(231, 229, 220); width: 99%; overflow: hidden; margin: 12px 0pt ! important; padding: 0px 3px 3px 0px; }.src_container .titlebar { background-color: rgb(212, 223, 255); border-width: 1px 1px 0pt; border-style: solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; padding: 3px 24px; margin: 0pt; width: auto; line-height: 120%; overflow: hidden; text-align: left; font-size: 12px; }.src_container .toolbar { display: inline; font-weight: normal; font-size: 100%; float: right; color: rgb(0, 0, 255); text-align: left; overflow: hidden; }.toolbar span.button { display: inline; font-weight: normal; font-size: 100%; color: rgb(0, 0, 255); text-align: left; overflow: hidden; cursor: pointer; }.src_container div.clientarea { background-color: white; border: 1px solid rgb(79, 129, 189); margin: 0pt; width: auto ! important; height: auto; overflow: auto; text-align: left; font-size: 12px; font-family: “Courier New”,”Consolas”,”Fixedsys”,courier,monospace,serif; }.src_container ol.mainarea { padding: 0pt 0pt 0pt 52px; margin: 0pt; background-color: rgb(247, 247, 255) ! important; }.number_show { padding-left: 52px ! important; list-style: decimal outside none ! important; }.number_show li { list-style: decimal outside none ! important; border-left: 1px dotted rgb(79, 129, 189); }.number_hide { padding-left: 0px ! important; list-style-type: none ! important; }.number_hide li { list-style-type: none ! important; border-left: 0px none; }ol.mainarea li { display: list-item ! important; font-size: 12px ! important; margin: 0pt ! important; line-height: 18px ! important; padding: 0pt 0pt 0pt 0px ! important; background-color: rgb(247, 247, 255) ! important; color: rgb(79, 129, 189); }ol.mainarea li pre { color: black; line-height: 18px; padding: 0pt 0pt 0pt 12px ! important; margin: 0em; background-color: rgb(255, 255, 255) ! important; }.linewrap ol.mainarea li pre { white-space: pre-wrap; word-wrap: break-word; }ol.mainarea li pre.alt { background-color: rgb(247, 247, 255) ! important; }name                                               senior                                             level
————————————————– ————————————————– ———–
新建                                                 文件                                                 1

第三条记录:
NAME=’使用当前连接查询’,同样不符合定位点条件,将进入递归:

SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
where a.senior = b.name
AND a.senior = ‘新建’
AND a.name = ‘使用当前连接查询’

同样,代入当前记录条件,下级递归b.name=’新建’,由于’新建’还不符合定位点条件,所以还将继续递归,及lmenu b表示子查询:

select c.name,c.senior,d.level+1 level from menu c,lmenu d
where c.senior = d.name
and c.name = ‘新建’
and c.senior = ‘文件’

替换成上述语句后,d.name=’文件’,将再次判断是否需要继续递归,由于’文件’符合终止递归条件,所以将终止递归。
我们用子查询表示第三条记录的递归过程如下:

显示行号 复制代码 SQL
  1. SELECT a.name,a.senior,b.level+1 level FROM menu A,(
  2.         select c.name,c.senior,d.level+1 level from menu c,(
  3.           select name,senior,0 level from menu where senior is null and name='文件'
  4.     ) d
  5.     where c.senior = d.name
  6.         and c.name = '新建'
  7.         and c.senior = '文件'   
  8. ) b
  9. where a.senior = b.name
  10.     and a.senior = '新建'
  11.     and a.name = '使用当前连接查询'

.src_container { background-color: rgb(231, 229, 220); width: 99%; overflow: hidden; margin: 12px 0pt ! important; padding: 0px 3px 3px 0px; }.src_container .titlebar { background-color: rgb(212, 223, 255); border-width: 1px 1px 0pt; border-style: solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; padding: 3px 24px; margin: 0pt; width: auto; line-height: 120%; overflow: hidden; text-align: left; font-size: 12px; }.src_container .toolbar { display: inline; font-weight: normal; font-size: 100%; float: right; color: rgb(0, 0, 255); text-align: left; overflow: hidden; }.toolbar span.button { display: inline; font-weight: normal; font-size: 100%; color: rgb(0, 0, 255); text-align: left; overflow: hidden; cursor: pointer; }.src_container div.clientarea { background-color: white; border: 1px solid rgb(79, 129, 189); margin: 0pt; width: auto ! important; height: auto; overflow: auto; text-align: left; font-size: 12px; font-family: “Courier New”,”Consolas”,”Fixedsys”,courier,monospace,serif; }.src_container ol.mainarea { padding: 0pt 0pt 0pt 52px; margin: 0pt; background-color: rgb(247, 247, 255) ! important; }.number_show { padding-left: 52px ! important; list-style: decimal outside none ! important; }.number_show li { list-style: decimal outside none ! important; border-left: 1px dotted rgb(79, 129, 189); }.number_hide { padding-left: 0px ! important; list-style-type: none ! important; }.number_hide li { list-style-type: none ! important; border-left: 0px none; }ol.mainarea li { display: list-item ! important; font-size: 12px ! important; margin: 0pt ! important; line-height: 18px ! important; padding: 0pt 0pt 0pt 0px ! important; background-color: rgb(247, 247, 255) ! important; color: rgb(79, 129, 189); }ol.mainarea li pre { color: black; line-height: 18px; padding: 0pt 0pt 0pt 12px ! important; margin: 0em; background-color: rgb(255, 255, 255) ! important; }.linewrap ol.mainarea li pre { white-space: pre-wrap; word-wrap: break-word; }ol.mainarea li pre.alt { background-color: rgb(247, 247, 255) ! important; }name                                               senior                                             level
————————————————– ————————————————– ———–
使用当前连接查询                                           新建                                                 2

第四条记录与第三条记录的递归层次完全一样。

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏