sqlserver列递归遍历表里面的数据 sqlserver递归 sql语句高级应用 sql递归查询 sql查询优化 sql组织结构查询优化 sqlwith使用解决思路

select * from tab_orgselect * from tab_orgcodeselect * from tab_org where node_id='010001'select distinct node2 from tab_org where node1='010001' and node2 is not null and node2<>''select distinct node3 from tab_org where node2 in(      select distinct node2 from tab_org       where node1='010001' and node2 is not null and node2<>'') and node3 is not null and node3<>''select distinct node4 from tab_org where node3 in(      select distinct node3 from tab_org       where node2 in(        select distinct node2 from tab_org         where node1='010001' and node2 is not null and node2<>''      ) and node3 is not null and node3<>'') and node3 is not null and node3<>''/**这样的查询一共有12级可以在中间任何一个节点进行查询归属该节点下面的节点**请高手指点*/

/*标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间：2008-05-12地点：广东深圳*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))insert into tb values('001' , null  , '广东省')insert into tb values('002' , '001' , '广州市')insert into tb values('003' , '001' , '深圳市')insert into tb values('004' , '002' , '天河区')insert into tb values('005' , '003' , '罗湖区')insert into tb values('006' , '003' , '福田区')insert into tb values('007' , '003' , '宝安区')insert into tb values('008' , '007' , '西乡镇')insert into tb values('009' , '007' , '龙华镇')insert into tb values('010' , '007' , '松岗镇')go--查询指定节点及其所有子节点的函数create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)asbegin  declare @level int  set @level = 1  insert into @t_level select @id , @level  while @@ROWCOUNT > 0  begin    set @level = @level + 1    insert into @t_level select a.id , @level    from tb a , @t_Level b    where a.pid = b.id and b.level = @level - 1  end  returnendgo--调用函数查询001(广东省)及其所有子节点select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id/*id   pid  name       ---- ---- ---------- 001  NULL 广东省002  001  广州市003  001  深圳市004  002  天河区005  003  罗湖区006  003  福田区007  003  宝安区008  007  西乡镇009  007  龙华镇010  007  松岗镇（所影响的行数为 10 行）*/--调用函数查询002(广州市)及其所有子节点select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id/*id   pid  name       ---- ---- ---------- 002  001  广州市004  002  天河区（所影响的行数为 2 行）*/--调用函数查询003(深圳市)及其所有子节点select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id/*id   pid  name       ---- ---- ---------- 003  001  深圳市005  003  罗湖区006  003  福田区007  003  宝安区008  007  西乡镇009  007  龙华镇010  007  松岗镇（所影响的行数为 7 行）*/drop table tbdrop function f_cid