sql server CTE递归使用测试

--CTE递归查询
if(object_id(N'menu') > 0)
	drop table menu

CREATE TABLE MENU 
 (
    name nvarchar(50) NOT NULL PRIMARY KEY,
    senior nvarchar(50) NULL
);
 
 INSERT INTO MENU values
    ('文件',NULL),
    ('新建','文件'),
    ('项目','新建'),
    ('项目11111','项目'),
    ('项目22222','项目'),
    ('项目33333','项目'),
    ('使用当前连接查询','新建');
    
WITH lmenu(name,senior,level) as
 (
    SELECT NAME,SENIOR,0 level FROM MENU WHERE name = '新建'
    UNION ALL
    SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
    where a.senior = b.name
)
SELECT *  from lmenu