mysql根据父级编码得到父级内容_在mysql查询中按父获取所有子级

I have a table with userid, managerid as follows:

id manager

------- -------

admin (NULL)

james admin

user james

workad user

creator workad

Now I want all the children (descendants) for one user id. In other words, for the userid james i need the children user, workad, creator. Because james is the top parent (ancestor). Is there any query to fetch result like this in mysql... Thanks in advance.

解决方案

For that you need to have a stored function :

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$

CREATE FUNCTION `GetFamilyTree` (GivenID VARCHAR(1024)) RETURNS varchar(1024) CHARSET latin1

DETERMINISTIC

BEGIN

DECLARE rv,q,queue,queue_children,front_id VARCHAR(1024);

DECLARE queue_length,pos INT;

SET rv = '';

SET queue = GivenID;

SET queue_length = 1;

WHILE queue_length > 0 DO

SET front_id = queue;

IF queue_length = 1 THEN

SET queue = '';

ELSE

SET pos = LOCATE(',',queue) + 1;

SET q = SUBSTR(queue,pos);

SET queue = q;

END IF;

SET queue_length = queue_length - 1;

SELECT IFNULL(qc,'') INTO queue_children

FROM (SELECT GROUP_CONCAT(id) qc

FROM Table1 WHERE manager = front_id) A;

IF LENGTH(queue_children) = 0 THEN

IF LENGTH(queue) = 0 THEN

SET queue_length = 0;

END IF;

ELSE

IF LENGTH(rv) = 0 THEN

SET rv = queue_children;

ELSE

SET rv = CONCAT(rv,',',queue_children);

END IF;

IF LENGTH(queue) = 0 THEN

SET queue = queue_children;

ELSE

SET queue = CONCAT(queue,',',queue_children);

END IF;

SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;

END IF;

END WHILE;

RETURN rv;

END $$

Then you can call like :

SELECT `id`, `manager`,GetFamilyTree(`id`) as children

from Table1;

You can have filters as well:

SELECT `id`, `manager`,GetFamilyTree(`id`) as children

from Table1 where `id` = 'james';