在MySQL中获取分层数据 [英] GET Hierarchical data in MySQL
本文介绍了在MySQL中获取分层数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想获取查询中给定孩子以下所有孩子的详细信息 例如. 如果我给出4级或(等级N),那么我想获取所有等级的所有详细信息(如L1到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.. :-)
这篇关于在MySQL中获取分层数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文