MySQL父子节点查询

1 CREATE TABLE`agency` (
2   `id` varchar(32) NOT NULL COMMENT '编号',
3   `name` varchar(30) NOT NULL COMMENT '名称',
4   `addr` varchar(100) NOT NULL COMMENT '位置',
5   `pId` varchar(32) DEFAULT NULL COMMENT '父ID',
6   PRIMARY KEY(`id`)
7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1 CREATE FUNCTION `findParent`(`subId` varchar(32)) RETURNS varchar(4000)
2 BEGIN
3 DECLARE sTemp VARCHAR(1000);
4 DECLARE sTempPar VARCHAR(1000);
5 SET sTemp = '';
6 SET sTempPar =subId;
7 #循环递归
8 WHILE sTempPar is not nullDO
9 #判断是否是第一个，不加的话第一个会为空
10     IF sTemp != '' THEN
11         SET sTemp = concat(sTemp,',',sTempPar);
12     ELSE
13         SET sTemp =sTempPar;
14     END IF;
15     SET sTemp = concat(sTemp,',',sTempPar);
16     SELECT group_concat(pid) INTO sTempPar FROM agency where pid<>id and FIND_IN_SET(id,sTempPar)>0; 
17 END WHILE;
18 RETURNsTemp;
19 END

1 CREATE FUNCTION `findSubNode`(`orgid` varchar(32)) RETURNS varchar(4000)
2 BEGIN
3 DECLARE oTemp VARCHAR(4000);
4 DECLARE oTempChild VARCHAR(4000);
5  SET oTemp = '';
6 SET oTempChild =orgid;
7  WHILE oTempChild IS NOT NULL
8 DO
9 SET oTemp = CONCAT(oTemp,',',oTempChild);
10 SELECT GROUP_CONCAT(id) INTO oTempChild FROM agency WHERE FIND_IN_SET(pId,oTempChild) > 0;
11 END WHILE;
12 RETURNoTemp;
13 END