SQL Recursive tree Child parent node queries each other

Source: Internet
Author: User
Tags rowcount

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.