用sql2008的CTE吧–自连接的递归处理  

一直用Oracle的自连接递归  connect by 非常好用.而在SQLServer中,很久以前开发时习惯使用2000版本,用存储过程实现递归处理,每次总是感觉效率很低处理很复杂.

最近想想换换吧,今天弄查了一下api开始用CTE实现效果,SQL2005以上版本支持

CTE的格式是 

WITH CTE名称(字段列表)   
 AS   
 (   
    单句查询语句   
 ) 

来创建个测试表

CREATE TABLE MENU   
 (  
    mName nvarchar(50) NOT NULL PRIMARY KEY,  
    priorName nvarchar(50) NULL 
);  

插入测试数据

INSERT INTO MENU values 
    ('文件',NULL),  
    ('新建','文件'),  
    ('项目','新建'),  
    ('保存','文件'); 

显示效果应该类似这样

文件   
    新建   
        项目   
     保存  

简单的CTE可以这样定义,但是看不到需要的效果

WITH lmenu(mName,priorName)   
as   
(   
    SELECT mName,priorName from menu   
) 
SELECT * FROM lmenu

但是这样得不到效果,只是可以执行.下面递归一下注意看联合的sql中进行的调用

WITH lmenu(mName,priormName,level) as 
 (  
    SELECT mName,priormName,0 level FROM MENU WHERE priormName IS NULL 
    UNION ALL 
    SELECT A.mName,A.priormName,b.level+1 FROM MENU A,lmenu b  
    where a.priormName = b.mName 
)   
SELECT *  from lmenu

结果如下

结果:

mName            priorName        level   
----------------------------------- -----------   
文件                NULL               0   
新建                文件                 1   
保存                文件                 1   
项目                新建                 2 

需要注意的是递归CTE有一些限制条件:

至少有一个定位点成员和一个递归成员,当然,你可以定义多个定位点成员和递归成员,但所有定位点成员必须在递归成员的前面

定位点成员之间必须使用UNION ALL、UNION、INTERSECT、EXCEPT集合运算符,最后一个定位点成员与递归成员之间必须使用UNION ALL,递归成员之间也必须使用UNION ALL连接

定位点成员和递归成员中的字段数量和类型必须完全一致

递归成员的FROM子句只能引用一次CTE对象

递归成员中不允许出现下列项

SELECT DISTINCT   
GROUP BY   
HAVING   
标量聚合   
TOP   
LEFTRIGHTOUTER JOIN(允许出现 INNER JOIN)   
子查询 

收工吧,近期实在太累.

请订阅本站 RSS feed 订阅到信箱 ,欢迎 Donate 或者 上面的广告内容 支持三十岁

本文链接:用sql2008的CTE吧--自连接的递归处理

转载声明:本站文章若无特别说明,皆为原创,转载请注明来源:三十岁,谢谢!^^


分享到:          
  • 通告关闭
  • 评论 (2)
  1. 上面俩都是好学生。什么都学,到哪里都学。。。
    我不是来学习的,我纯路过、。

    • 好学生已经被和谐了
      欢迎路过

评论关闭