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

为此你需要有一个存储函数：

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 $$

然后你可以这样打电话：

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

from Table1;

你也可以有过滤器：

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

from Table1 where `id` = 'james';