递归查询

CTE递归查询

 WITH CTE名称(字段列表) 
  AS 
  ( 
    查询语句 
  ) 
例如： 
WITH lmenu(name,senior) 
as 
( 
    SELECT name,senior from menu 
)

我们定义了一个名称为lmenu的CTE，这样我们后续即可直接使用lmenu来查询，如： 
SELECT * FROM lmenu

如果我们在定义CTE的查询语句中直接引用CTE表本身，

 --没有列名将select所有列
     WITH cte_name AS
     (
       SELECT * FROM dbo.TB_Form
     )
     SELECT * FROM cte_name

测试一：

--菜单目录结构表  
create table #tb_menu(  
 id int not null, --主键id  
title varchar(50), --标题  
 parentid int --parentid id  
);  
--父菜单  
insert into #tb_menu(id, title, parentid) values(1, '父菜单1',null);  
insert into #tb_menu(id, title, parentid) values(2, '父菜单2',null);  
insert into #tb_menu(id, title, parentid) values(3, '父菜单3',null);  
insert into #tb_menu(id, title, parentid) values(4, '父菜单4',null);  
insert into #tb_menu(id, title, parentid) values(5, '父菜单5',null);  
--一级菜单  
insert into #tb_menu(id, title, parentid) values(6, '一级菜单6',1);  
insert into #tb_menu(id, title, parentid) values(7, '一级菜单7',1);  
insert into #tb_menu(id, title, parentid) values(8, '一级菜单8',1);  
insert into #tb_menu(id, title, parentid) values(9, '一级菜单9',2);  
insert into #tb_menu(id, title, parentid) values(10, '一级菜单10',2);  
insert into #tb_menu(id, title, parentid) values(11, '一级菜单11',2);  
insert into #tb_menu(id, title, parentid) values(12, '一级菜单12',3);  
insert into #tb_menu(id, title, parentid) values(13, '一级菜单13',3);  
insert into #tb_menu(id, title, parentid) values(14, '一级菜单14',3);  
insert into #tb_menu(id, title, parentid) values(15, '一级菜单15',4);  
insert into #tb_menu(id, title, parentid) values(16, '一级菜单16',4);  
insert into #tb_menu(id, title, parentid) values(17, '一级菜单17',4);  
insert into #tb_menu(id, title, parentid) values(18, '一级菜单18',5);  
insert into #tb_menu(id, title, parentid) values(19, '一级菜单19',5);  
insert into #tb_menu(id, title, parentid) values(20, '一级菜单20',5);  
--二级菜单  
insert into #tb_menu(id, title, parentid) values(21, '二级菜单21',6);  
insert into #tb_menu(id, title, parentid) values(22, '二级菜单22',6);  
insert into #tb_menu(id, title, parentid) values(23, '二级菜单23',7);  
insert into #tb_menu(id, title, parentid) values(24, '二级菜单24',7);  
insert into #tb_menu(id, title, parentid) values(25, '二级菜单25',8);  
insert into #tb_menu(id, title, parentid) values(26, '二级菜单26',9);  
insert into #tb_menu(id, title, parentid) values(27, '二级菜单27',10);  
insert into #tb_menu(id, title, parentid) values(28, '二级菜单28',11);  
insert into #tb_menu(id, title, parentid) values(29, '二级菜单29',12);  
insert into #tb_menu(id, title, parentid) values(30, '二级菜单30',13);  
insert into #tb_menu(id, title, parentid) values(31, '二级菜单31',14);  
insert into #tb_menu(id, title, parentid) values(32, '二级菜单32',15);  
insert into #tb_menu(id, title, parentid) values(33, '二级菜单33',16);  
insert into #tb_menu(id, title, parentid) values(34, '二级菜单34',17);  
insert into #tb_menu(id, title, parentid) values(35, '二级菜单35',18);  
insert into #tb_menu(id, title, parentid) values(36, '二级菜单36',19);  
insert into #tb_menu(id, title, parentid) values(37, '二级菜单37',20);  
  
--三级菜单  
insert into #tb_menu(id, title, parentid) values(38, '三级菜单38',21);  
insert into #tb_menu(id, title, parentid) values(39, '三级菜单39',22);  
insert into #tb_menu(id, title, parentid) values(40, '三级菜单40',23);  
insert into #tb_menu(id, title, parentid) values(41, '三级菜单41',24);  
insert into #tb_menu(id, title, parentid) values(42, '三级菜单42',25);  
insert into #tb_menu(id, title, parentid) values(43, '三级菜单43',26);  
insert into #tb_menu(id, title, parentid) values(44, '三级菜单44',27);  
insert into #tb_menu(id, title, parentid) values(45, '三级菜单45',28);  
insert into #tb_menu(id, title, parentid) values(46, '三级菜单46',28);  
insert into #tb_menu(id, title, parentid) values(47, '三级菜单47',29);  
insert into #tb_menu(id, title, parentid) values(48, '三级菜单48',30);  
insert into #tb_menu(id, title, parentid) values(49, '三级菜单49',31);  
insert into #tb_menu(id, title, parentid) values(50, '三级菜单50',31);  
 
 --数据表需要有id和parentid
 WITH cte
  as 
    (
    --起始条件
    SELECT cast(row_number() over(order by id) as varchar(10)) cid,*,0 AS level FROM #tb_menu where  
    --ISNULL(parentid,0)=0  --没有父节点菜单
    --and id=6 --查询该节点下所有节点
    union ALL
    --递归条件
    select  cast(cid+cast(row_number() over(order by  a.id )as varchar(10)) as varchar(10)),a.*,cte.level+1 from #tb_menu a
    inner join cte --执行递归
    ON cte.id=ISNULL(a.parentid,0)
     )                       
    select  *  from cte  order by cid 

结果没有父节点菜单
查询该节点下所有节点

查询某节点上级节点

WITH cte
  as 
    (
    --起始条件level设置最高是3级
    SELECT cast(row_number() over(order by id) as varchar(10)) cid,*,3 AS level FROM #tb_menu 
    WHERE 
    id=44
    union ALL
    --递归条件注意level-1和on的条件互换
    select  cast(cid+cast(row_number() over(order by  a.id )as varchar(10)) as varchar(10)),a.*,cte.level-1 from #tb_menu a
    inner join cte
    --ON cte.id=ISNULL(a.parentid,0)
    ON cte.parentid=a.id
     )                       
    select  *  from cte  order by level ASC  --根据层级降序

结果

 WITH cte
  as 
    (
		SELECT id,title,parentid FROM #tb_menu 
		WHERE  id=44
		union ALL
		select a.id,a.title,a.parentid from #tb_menu a
		inner join cte
		ON cte.parentid=a.id
    )                       
    select  DISTINCT id,title,parentid   from cte  order by id ASC