MySQL recursively queries the current node and its subnode _ MySQL

MySQL recursively queries the current node and its subnode bitsCN.com

MySQL recursively queries the current node and its subnodes

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 ()> 0do set maid; 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.

BitsCN.com