SQL 递归树 子父节点相互查询

if object_id( '[tb]' ) is  not  null  drop  table  [tb]

go

create  table  [tb]([modeid] int ,modename varchar (20),parentid int )

insert  [tb]

select  100 , '商品管理' , 0 union  all

select  101 , '定单管理' , 0 union  all

select  102 , '用户管理' , 0 union  all

select  104 , '学院广告' , 0 union  all

select  105 , '系统设置' , 0 union  all

select  106 , '附件管理' , 0 union  all

select  107 , '商品管理' , 100 union  all

select  108 , '明细管理' , 100 union  all

select  109 , '物流管理' , 100 union  all

select  110 , '商品信息管理' , 107 union  all

select  111 , '商品分类管理' , 107 union  all

select  112 , '回收站管理' , 107 union  all

select  114 , '团购管理' , 108 union  all

select  115 , '拍卖管理' , 108 union  all

select  116 , '优惠管理' , 108 union  all

select  117 , '会员管理' , 102 union  all

select  118 , '会员卡管理' , 102 union  all

select  119 , '资金管理' , 102 union  all

select  120 , '管理员管理' , 102 union  all

select  121 , '添加管理员' , 120 union  all

select  122 , '修改管理员' , 120

go

  

  

--查所有子结点

if object_id( 'f_getC' ) is  not  null  drop  function  f_getC

go

create  function  f_getC(@id int )

returns  @re table (id int , level  int ,sort varchar (10))

as

begin

     declare  @l int

     set  @l=0

     insert  @re select  @id,@l, null

     while @@rowcount>0

     begin

         set  @l=@l+1

         insert  @re select  a.modeid,@l,ltrim( isnull (b.sort,a.modeid)) from  tb as  a,@re as  b

  where  b.id=a.parentid and  b. level =@l-1

     end

     update  @re set  level  = level  -1

     return

end

go

  

  

select  a.modeid,a.parentid,REPLICATE( '  ' ,b. level ) + '┝' +a.modename,b. level ,b.sort from  tb  a,f_getC(0) b

where  a.modeid=b.id

order  by  case  when  b. level <2 then  0 else  1 end ,b.sort,b. level

  

/*

modeid      parentid                                                       sort       level     

----------- ----------- -------------------------------------------------- ---------- -----------

100         0           ┝商品管理                                              100        0

107         100           ┝商品管理                                            100        1

108         100           ┝明细管理                                            100        1

109         100           ┝物流管理                                            100        1

101         0           ┝定单管理                                              101        0

102         0           ┝用户管理                                              102        0

117         102           ┝会员管理                                            102        1

118         102           ┝会员卡管理                                           102        1

119         102           ┝资金管理                                            102        1

120         102           ┝管理员管理                                           102        1

104         0           ┝学院广告                                              104        0

105         0           ┝系统设置                                              105        0

106         0           ┝附件管理                                              106        0

110         107             ┝商品信息管理                                        100        2

111         107             ┝商品分类管理                                        100        2

112         107             ┝回收站管理                                         100        2

114         108             ┝团购管理                                          100        2

115         108             ┝拍卖管理                                          100        2

116         108             ┝优惠管理                                          100        2

121         120             ┝添加管理员                                         102        2

122         120             ┝修改管理员                                         102        2

  

（所影响的行数为 21 行）

  

*/

  

  

--查所有子结点,带路径与排序

if object_id( 'f_getC' ) is  not  null  drop  function  f_getC

go

create  function  f_getC(@id int )

returns  @re table (id int , level  int ,sort varchar (100),path varchar (500))

as

begin

     declare  @l int

     set  @l=0

     insert  @re

  select  [modeid],@l, right ( '00000' +ltrim(modeid),5),modename

  from  tb where  parentid=@id

     while @@rowcount>0

     begin

         set  @l=@l+1

         insert  @re

   select  a.modeid,@l,b.sort+ right ( '00000' +ltrim(a.modeid),5),

       b.path+ ' - ' +a.modename

   from  tb as  a,@re as  b

   where  b.id=a.parentid and  b. level =@l-1

     end

     update  @re set  level  = level

     return

end

go

  

select  a.modeid,a.parentid,REPLICATE( '  ' ,b. level ) + '┝' +a.modename,b. level ,b.sort ,b.path from  tb  a,f_getC(0) b

where  a.modeid=b.id

order  by  sort

  

/*

modeid      parentid                         level                          

----------- ----------- -------------------- ----------- -------------------- ----------------------------------------

100         0           ┝商品管理                0           00100                商品管理

107         100           ┝商品管理              1           0010000107           商品管理 - 商品管理

110         107             ┝商品信息管理          2           001000010700110      商品管理 - 商品管理 - 商品信息管理

111         107             ┝商品分类管理          2           001000010700111      商品管理 - 商品管理 - 商品分类管理

112         107             ┝回收站管理           2           001000010700112      商品管理 - 商品管理 - 回收站管理

108         100           ┝明细管理              1           0010000108           商品管理 - 明细管理

114         108             ┝团购管理            2           001000010800114      商品管理 - 明细管理 - 团购管理

115         108             ┝拍卖管理            2           001000010800115      商品管理 - 明细管理 - 拍卖管理

116         108             ┝优惠管理            2           001000010800116      商品管理 - 明细管理 - 优惠管理

109         100           ┝物流管理              1           0010000109           商品管理 - 物流管理

101         0           ┝定单管理                0           00101                定单管理

102         0           ┝用户管理                0           00102                用户管理

117         102           ┝会员管理              1           0010200117           用户管理 - 会员管理

118         102           ┝会员卡管理             1           0010200118           用户管理 - 会员卡管理

119         102           ┝资金管理              1           0010200119           用户管理 - 资金管理

120         102           ┝管理员管理             1           0010200120           用户管理 - 管理员管理

121         120             ┝添加管理员           2           001020012000121      用户管理 - 管理员管理 - 添加管理员

122         120             ┝修改管理员           2           001020012000122      用户管理 - 管理员管理 - 修改管理员

104         0           ┝学院广告                0           00104                学院广告

105         0           ┝系统设置                0           00105                系统设置

106         0           ┝附件管理                0           00106                附件管理

  

(21 行受影响)

  

  

*/

  

----------