mysql 编码分层_在MySQL中获取分层数据

I want to get the details of of all child below given child in query

Eg.

If i give Level 4 or (LEVEL N) then I want to get All Details of all Level (Like L1 to L4/LN)

解决方案

CREATE TABLE:

DROP TABLE IF EXISTS pctable;

CREATE TABLE pctable

(

id INT NOT NULL AUTO_INCREMENT,

parent_id INT,

PRIMARY KEY (id)

) ENGINE=MyISAM;

INSERT QUERY:

INSERT INTO pctable (parent_id) VALUES (0);

INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;

SELECT * FROM pctable;

CREATE FUNCTION:

DELIMITER $$

DROP FUNCTION IF EXISTS `GetFamilyTree1` $$

CREATE FUNCTION `GetFamilyTree1` (GivenID INT) RETURNS varchar(1024) CHARSET latin1

DETERMINISTIC

BEGIN

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

DECLARE queue_length,front_id,pos INT;

SET rv = '';

SET queue = GivenID;

SET queue_length = 1;

WHILE queue_length > 0 DO

SET front_id = FORMAT(queue,0);

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 pctable WHERE parent_id = 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 $$

DELIMITER ;

NOW FIRE SELECT QUERY:

SELECT id,GetFamilyTree1(id) FROM pctable where id = 2;

****Above Query will give child of id 2.****

After 2 days i got solution.. :-)