SQL Server 中的CTE递归查询

 

1.创建表

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

 

 

WITH lmenu(name,senior)
as
(
    SELECT name,senior from menu
)

 

 

2.语句

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

 

源文来自:http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html