mysql树形结构的查询案例

表结构

这种情况可以通过左连接实现

可以看到左连接是以左表为基准，通过关联关系id = pid去找到对应的上级组织记录，所以空的id找不到对应的记录，返回空

有时候我们需要获取某个组织的完整路径 如

部门C/部门C_2/部门C_2_1/部门C_2_1_1

编写存储过程，生成一个临时表tmpLst，按照层级把每一条记录插入到临时表，然后每次从临时表查当前层级的组织，循环去查组织表的上级组织，直到结果ROW_COUNT = 0为止，代表当前层级下的所有组织已经是最后一级

查询到的结果，大家可以自行优化一下显示方式和查询的字段

当然还有另一种方式，从设计上解决

如新加一个唯一约束，把组织的约束定义为 ORG_001_ORG_001_002_ORG_001_003 这样的形式

当需要查询ORG_001所有的下级时，只需要查询约束 like ORG_001% 即可

当需要查询ORG_001_002所有上级时，只需要查询约束 like %ORG_001_002

不过问题在于如果组织的存在架构调整，如，ORG_001_002调整到了 ORG_002下，因为树型结构变化了，直接用like无法查询到正确数据，这个时候要考虑是否允许调整或者调整后修改对应的唯一约束

1，建立测试表和数据：

DROP TABLE IF EXISTS csdn.channel

CREATE TABLE csdn.channel (

id INT(11) NOT NULL AUTO_INCREMENT,

cname VARCHAR(200) DEFAULT NULL,

parent_id INT(11) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO channel(id,cname,parent_id)

VALUES (13,'首页',-1),

(14,'TV580',-1),

(15,'生活580',-1),

(16,'左上幻灯片',13),

(17,'帮忙',14),

(18,'栏目简介',17)

DROP TABLE IF EXISTS channel

2，利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：

2.1，从某节点向下遍历子节点，递归生成临时表数据

-- pro_cre_childlist

DELIMITER $$

DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist$$

CREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT,IN nDepth INT)

BEGIN

DECLARE done INT DEFAULT 0

DECLARE b INT

DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

SET max_sp_recursion_depth=12

INSERT INTO tmpLst VALUES (NULL,rootId,nDepth)

OPEN cur1

FETCH cur1 INTO b

WHILE done=0 DO

CALL pro_cre_childlist(b,nDepth+1)

FETCH cur1 INTO b

END WHILE

CLOSE cur1

END$$

2.2，从某节点向上追溯根节点，递归生成临时表数据

-- pro_cre_parentlist

DELIMITER $$

DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist$$

CREATE PROCEDURE csdn.pro_cre_parentlist(IN rootId INT,IN nDepth INT)

BEGIN

DECLARE done INT DEFAULT 0

DECLARE b INT

DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

SET max_sp_recursion_depth=12

INSERT INTO tmpLst VALUES (NULL,rootId,nDepth)

OPEN cur1

FETCH cur1 INTO b

WHILE done=0 DO

CALL pro_cre_parentlist(b,nDepth+1)

FETCH cur1 INTO b

END WHILE

CLOSE cur1

END$$

---