oracle中的connect by 在sql server中实现

1。Oracle中：
select org_id
from organizations
start with org_id= :org_id
connect by prior org_id=supervision_org_id
order by supervision_org_id

2。Sql Functiong:
 FunctionNo SubFunctionNo SubFunctionType  
  01.   systemadmin Privilege 0  
  01.   systemadmin subsysadmin 1(表示树枝)  
  subsysadmin subsys1 0(表示leaf)  
  subsysadmin subsys2 0  
  subsysadmin User 1  
  User UserOne 0  
   
  請教實現此層次結構輸出的函數(在oracle中可以使用connect   by實現此功能)  

3。sql语句实现：
举例：  
   
  declare   @a   table   (TC_Id   int,TC_PID   int,TC_Name   varchar(200))  
  insert   @a   values(1,0,'中国')  
  insert   @a   values(2,0,'美国')  
  insert   @a   values(3,0,'加拿大')  
  insert   @a   values(4,1,'北京')  
  insert   @a   values(5,1,'上海')  
  insert   @a   values(6,1,'江苏')  
  insert   @a   values(7,6,'苏州')  
  insert   @a   values(8,7,'常熟')  
  insert   @a   values(9,6,'南京')  
  insert   @a   values(10,6,'无锡')  
  insert   @a   values(11,2,'纽约')  
  insert   @a   values(12,2,'旧金山')  
   
  declare   @tmp1   table   (TC_Id   int,TC_PID   int,TC_Name   varchar(200),lev   int)  
  insert   @tmp1   select   *,1   from   @a   where   tc_ID=1  
  while   exists(select   1   from   @a   a,@tmp1   b   where   a.tc_pid=b.tc_ID   and   a.tc_ID   not   in   (select   tc_ID   from   @tmp1))  
      insert   @tmp1   select   a.*,1   from     @a   a,@tmp1   b   where   a.tc_pid=b.tc_ID   and   a.tc_ID   not   in   (select   tc_ID   from   @tmp1)  
  select   *   from   @tmp1

  

 

select max(tjr_dj) from userinfo

select * from userinfo where tjr_dj = 7

select * from userinfo where tjr_id = 81

 

declare @tmp1 table (user_id int, tjr_id int, tjr_dj int, user_name varchar(64))  
insert @tmp1 select user_id, tjr_id, tjr_dj, user_name from userinfo where user_id = 81  
  while exists(select 1 from userinfo a, @tmp1 b where a.tjr_id = b.user_id and a.user_id not in (select user_id from @tmp1))  
      insert @tmp1 select a.user_id, a.tjr_id, a.tjr_dj, a.user_name  from userinfo a, @tmp1 b where a.tjr_id = b.user_id and a.user_id not in (select user_id from @tmp1)  
select * from @tmp1 order by tjr_id