Mysql according to the parent node id for the parent node and all child nodes



Incoming id is 1 for all database under the baoji county county-rural

Using mysql and mybatis

CodePudding user response:

Need to write a function

CodePudding user response:

Query data structure through calendar calculation method, using a recursive or stack to operate, in simple terms:
A stack to store intermediate nodes, in the process of a stack to store the result nodes,
The direct child nodes of the root node into the stack 1;
A node, removed from a stack 1 into the stack 2; And find all its children into the stack 1; Repeat the steps until the stack is empty, 1
2 is all the child nodes of the stack,

CodePudding user response:

Or to find a way to in the code, such as similar to the postal code, code is classified, 518000 is 518 XXX root node ~ ~
Query classification coding efficiency is much higher, but complex, update and review whether to look at your coding system is not stable, want to often change,

CodePudding user response:

If can add a list of table structure, can we change the path used to record the path, such as xiguan -/0-1-2, statistical 1 all subsets of direct path contains 0

CodePudding user response:

CodePudding user response:

CodePudding user response:

 DROP FUNCTION IF the EXISTS diguihanshu; 
The CREATE FUNCTION diguihanshu (areaId INT) 
RETURNS a VARCHAR (4000) 
The BEGIN 
DECLARE the sid VARCHAR (4000); 
DECLARE sidChd VARCHAR (4000); 

SET the sid='$'; 
The SET sidChd=CAST (areaId AS CHAR); 

WHILE sidChd IS NOT NULL DO 
SET the sid=CONCAT (sid, ', ', sidChd); 
The SELECT GROUP_CONCAT (id) INTO sidChd FROM digui WHERE FIND_IN_SET (pid, sidChd) & gt; 0; 
END the WHILE; 
RETURN the sid; 
The END; 

SELECT * FROM digui WHERE FIND_IN_SET (id, diguihanshu (6)); 

CodePudding user response:

The above is to create a stored procedure, the following is the query

 SELECT * FROM digui WHERE FIND_IN_SET (id, diguihanshu (1));