MySQL recursively queries the current node and Its subnodes

Welcome to the Linux community forum and interact with 2 million technical staff. 1. Table Structure 2. query statement begin (iniddvarchar (36) begindeclarelevint; setlev1; success; CREATETABLEtmp1 (IDVARCHAR (36), OrgNamevarch

Welcome to the Linux community forum and interact with 2 million technical staff> enter 1. table Structure 2. query statement create procedure Pro_GetUnderOrg (in idd varchar (36) begindeclare levint; set lev= 1; drop table if exists tmp1; create table tmp1 (id varchar (36), OrgName varch

Welcome to the Linux community forum and interact with 2 million technicians>

1. Table Structure

2. query statement

Create procedure Pro_GetUnderOrg (in idd varchar (36)

Begindeclare levint;

Set lev_= 1;

Drop table if exists tmp1;

Create table tmp1 (id varchar (36), OrgName varchar (50), ParentID varchar (36), levv INT );

INSERT tmp1 select id, OrgName, ParentID, 1 FROM 'organization' WHERE ParentID = idd;

While row_count ()> 0 do

Set lev_= lev_+ 1;

INSERT tmp1 SELECT t. ID, t. 'orgname', t. 'parentid', levfrom organization t join tmp1 a on t. parentId =. id and levv = lev-1; end while;

INSERT tmp1 select id, OrgName, ParentID, 0 FROM 'organization' where id = idd;

SELECT * FROM tmp1;

End;

3. Call

Call Pro_GetUnderOrg ('9b2ae044-3b51-47fa-a24c-8326ba7b9fd3 ');

4. Note: After the design of this table, you must add a path field, so you don't have to worry about it. You can directly like the query.