用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   
LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）   
子查询 

收工吧,近期实在太累.

分享到：