请选择 进入手机版 | 继续访问电脑版
贠老师培训群:150322713    贠老师QQ:767708506

贠老师office培训-excel学习网

 找回密码
 立即注册
点击咨询贠老师
查看: 185|回复: 5

SQL Server 2008中的CTE递归查询

[复制链接]

607

主题

604

帖子

1909

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1909
发表于 2016-1-7 23:32:00 | 显示全部楼层 |阅读模式
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='新建'
回复

使用道具 举报

3

主题

1042

帖子

67

积分

注册会员

Rank: 2

积分
67
发表于 2017-3-6 12:22:39 | 显示全部楼层
一下子解决了我工作当中的难题,谢谢楼主,非常感谢!
回复 支持 反对

使用道具 举报

2

主题

972

帖子

87

积分

注册会员

Rank: 2

积分
87
发表于 2017-9-16 18:47:25 | 显示全部楼层
先收藏再学习,谢谢啦!
回复 支持 反对

使用道具 举报

0

主题

922

帖子

6

积分

注册会员

Rank: 2

积分
6
发表于 2018-7-30 19:27:01 | 显示全部楼层
楼主是好人哪,感谢分享,太棒了!
回复 支持 反对

使用道具 举报

1

主题

1111

帖子

76

积分

注册会员

Rank: 2

积分
76
发表于 2018-12-15 12:42:48 | 显示全部楼层
还有许多问题不明白,有点恼火啊!
回复 支持 反对

使用道具 举报

5

主题

543

帖子

73

积分

注册会员

Rank: 2

积分
73
发表于 2019-1-2 22:11:16 | 显示全部楼层
有些不是太明白,研究研究再说
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则



陕ICP备15003731号  

贠老师培训 GMT+8, 2019-1-20 03:33 , Processed in 0.235719 second(s), 29 queries .

快速回复 返回顶部 返回列表