SQL Recursive tree Child parent node queries each other

If object_id (' [TB] ') is not null drop table [tb]gocreate table [TB] ([Modeid] int,modename varchar (), ParentID int) insert [Tb]select 100, ' merchandise management ', 0 Union Allselect 101, ' Order Management ', 0 Union allselect 102, ' User Management ', 0 Union allselect 104, ' Academy advertisement ', 0 Uni On Allselect 105, ' System Setup ', 0 Union allselect 106, ' attachment Management ', 0 Union allselect 107, ' Commodity management ', ' Union allselect 108 ', ' detail management ', 1 The Union Allselect 109, ' Logistics management ', the Union Allselect 110, ' Commodity information management ', 107 Union Allselect 111, ' Commodity classification management ', 107 union Allselect  112, ' Recycle Bin Management ', 107 Union Allselect 114, ' buy management ', 108 Union allselect 115, ' Auction Management ', 108 Union allselect 116, ' preferential management ', 108 union Allselect 117, ' member Management ', 102 Union Allselect 118, ' Membership card management ', 102 Union allselect 119, ' money Management ', 102 Union Allselect 120, ' admin management  ', 102 Union allselect 121, ' add admin ', ' allselect ' 122, ' modify admin ', 120go--Check all child nodes if object_id (' f_getc ') is not null Drop function F_getcgocreate function f_getc (@id int) returns @re table (ID int,level int,sort varchar) asbegin Dec Lare @l int Set @l=0    Insert @re Select @id, @l,null while @ @rowcount >0 begin set @[email protected]+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 [email protected] End Update @re Set level = Level-1 Returnendgo Select A.modeid,a.parentid,replicate (", b.level) + ' ┝ ' +a.mo Dename,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┝ Merchandise Management                                            100 0107 100┝ Commodity Management           100 1108 100┝ Detail Management 100 1109 100               ┝ Logistics Management                             100 1101 0┝ Order Management 101 0102 0┝ User Management 102 0117 102┝ member                                           Management 102 1118 102┝ membership card management         102 1119 102┝ Capital Management 102 1120                                              102┝ Admin Management 102 1104 0┝ College AD        104 0105 0┝ System Setup 105 0106 0┝ Accessory Management 106 0110 107┝ Business                                        Product Information Management 100 2111 107┝ Commodity Classification Management 100 2112 107┝ Recycle Bin management 100 2114 108┝ Group Purchase Management                                          100 2115 108┝ Auction Management             100 2116 108┝ Preferential Management 100 2121 120                                         ┝ Add Administrator 102 2122 120┝ Modify Administrator 102 2 (the number of rows affected is 21 rows) */--Check all sub-nodes, with path and sort if object_id (' f_getc ') is not null drop function f_ getcgocreate function f_getc (@id int) returns @re table (ID int,level int,sort varchar (+), path varchar) Asbegin D Eclare @l int Set @l=0 insert @re SELECT [Modeid],@l,right (' 00000 ' +ltrim (Modeid), 5), modename from TB where [EMAIL&N Bsp;protected] While @ @rowcount >0 begin set @[email protected]+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 [email protected] End Update @re Set level = Level Returnendgo 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┝ Merchandise Management 0 00100 Merchandise Management 107          100┝ Merchandise Management 1 0010000107 Commodity management-commodity management 110 107┝ commodity information management 2 001000010700110 Commodity Management-commodity management-commodity information Management 111 107┝ Commodity Classification Management 2 00100 0010700111 Commodity Management-Commodity management-commodity classification Management 112 107┝ Recycle Bin Management 2 001000010700112 Commodity management-business Product Management-Recycle Bin Management 108 100┝ Detail Management 1 0010000108 Commodity management-Detail Management 114 108┝ Group Purchase Management 2       001000010800114 Merchandise Management-Detail Management-group Purchase Management 115 108┝ Auction Management 2 001000010800115 Commodity Management-Detail Management-Auction Management 116 108┝ Preferential Management 2 001000010800116 merchandise Management-detail management-preferential management 10                9 100┝ Logistics Management 1 0010000109 Merchandise Management-Logistics Management 101 0┝ Order Management                0 00101 Order Management 102 0┝ user Management 0 00102           User Management 117 102┝ member management 1 0010200117 User Management-member management 118 102 ┝ Membership card Management 1 0010200118 User Management-membership card management 119 102┝ money management 1 0 010200119 User Management-money Management 120 102┝ Admin Management 1 0010200120 User Management-Admin Management 1          21 120   ┝ Add admin 2 001020012000121 user admin-admin admin-add Admin 122 120┝ Modify Administrator 2                001020012000122 User Management-Admin admin-Modify Admin 104 0┝ College AD 0 00104                College AD 105 0┝ System Setup 0 00105 System Setup 106 0┝ Attachment Management 0 00106 Attachment Management (21 rows affected) */----------

　　

SQL Recursive tree Child parent node queries each other