父子结点递归查询
<h1>表结构</h1>
<pre><code>CREATE TABLE `agency` (
`id` varchar(32) NOT NULL COMMENT '编号',
`name` varchar(30) NOT NULL COMMENT '名称',
`addr` varchar(100) NOT NULL COMMENT '位置',
`pId` varchar(32) DEFAULT NULL COMMENT '父ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code></pre>
<h1>表数据</h1>
<pre><code>select * from agency</code></pre>
<p><img src="https://www.showdoc.cc/server/api/common/visitfile/sign/a0d372250d21da2aae497e45493b2a97?showdoc=.jpg" alt="" /></p>
<h1>查询子结点的所有父结点</h1>
<pre><code>CREATE FUNCTION `findParent`(`subId` varchar(32)) RETURNS varchar(4000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp = '';
SET sTempPar =subId;
#循环递归
WHILE sTempPar is not null DO
#判断是否是第一个，不加的话第一个会为空
IF sTemp != '' THEN
SET sTemp = concat(sTemp,',',sTempPar);
ELSE
SET sTemp = sTempPar;
END IF;
SET sTemp = concat(sTemp,',',sTempPar);
SELECT group_concat(pid) INTO sTempPar FROM agency where pid<>id and FIND_IN_SET(id,sTempPar)>0;
END WHILE;
RETURN sTemp;
END</code></pre>
<h1>调用示例</h1>
<pre><code>select * from agency where FIND_IN_SET(id,findParent('6'))</code></pre>
<p><img src="https://www.showdoc.cc/server/api/common/visitfile/sign/1320bbfd0b4edbe1a5937a4e91549eef?showdoc=.jpg" alt="" /></p>
<h1>查询根结点的所有子结点</h1>
<pre><code>CREATE FUNCTION `findSubNode`(`orgid` varchar(32)) RETURNS varchar(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = orgid;
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM agency WHERE FIND_IN_SET(pId,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END</code></pre>
<h1>调用示例</h1>
<pre><code>select * from agency where FIND_IN_SET(id,findSubNode('2'))</code></pre>
<p><img src="https://www.showdoc.cc/server/api/common/visitfile/sign/f392ff322d356f1cf803286ef3aa7c02?showdoc=.jpg" alt="" /></p>