在MySQL中获取分层数据

本文介绍了在MySQL中获取分层数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想获取查询中给定孩子以下所有孩子的详细信息 例如. 如果我给出4级或(等级N)，那么我想获取所有等级的所有详细信息(如L1到L4/LN)

解决方案

从此链接中找到答案

创建表:

DROP TABLE IF EXISTS pctable; CREATE TABLE pctable ( id INT NOT NULL AUTO_INCREMENT, parent_id INT, PRIMARY KEY (id) ) ENGINE=MyISAM;

插入查询:

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;

创建功能:

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 ;

现在进行火灾选择查询:

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

****上面的查询将给ID为2的孩子.

两天后我得到了解决方案..:-)

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)

解决方案

Found the answer from this Link

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.. :-)