查询父节点
Drop FUNCTION if EXISTS `getParentList`;
CREATE FUNCTION `getParentList`(rootId varchar(100))
RETURNS varchar(1000)
BEGIN
DECLARE fid varchar(100) default '';
DECLARE str varchar(1000) default rootId;
WHILE rootId is not null do
SET fid =(SELECT cuntongncxx.NCXX_DS FROM cuntongncxx WHERE ID = rootId);
IF fid is not null THEN
SET str = concat(str, ',', fid);
SET rootId = fid;
ELSE
SET rootId = fid;
END IF;
END WHILE;
return str;
END
SELECT getParentList('JXW-001-002-001');
查询结果:
查询子节点 (不包含本节点)
DROP FUNCTION IF EXISTS `getChildList`;
CREATE FUNCTION `getChildList`(rootId varchar(2000))
RETURNS varchar(2000)
BEGIN
DECLARE cid varchar(2000) default rootId;
DECLARE str varchar(2000) default '$';
WHILE cid is not null do
SELECT GROUP_CONCAT(cuntongncxx.ID)into cid FROM cuntongncxx WHERE FIND_IN_SET(cuntongncxx.NCXX_DS,cid)>0;
IF cid is not NULL
THEN
set str=CONCAT(str,',',cid);
end IF;
END WHILE;
return str;
END
SELECT getChildList('JXW000001');
查询子节点 (包含本节点)
DROP FUNCTION IF EXISTS `getChildList`;
CREATE FUNCTION `getChildList`(rootId varchar(2000))
RETURNS varchar(2000)
BEGIN
DECLARE cid varchar(2000) default rootId;
DECLARE str varchar(2000) default '$';
WHILE cid is not null do
set str=CONCAT(str,',',cid);
SELECT GROUP_CONCAT(cuntongncxx.ID)into cid FROM cuntongncxx WHERE FIND_IN_SET(cuntongncxx.NCXX_DS,cid)>0;
END WHILE;
return str;
END
DROP FUNCTION IF EXISTS `getChildList`;
CREATE FUNCTION `getChildList`(rootId varchar(2000))
RETURNS varchar(2000)
BEGIN
DECLARE cid varchar(2000) default rootId;
DECLARE str varchar(2000) default '$';
WHILE cid is not null do
set str=CONCAT(str,',',cid);
SELECT GROUP_CONCAT(cuntongncxx.ID)into cid FROM cuntongncxx WHERE FIND_IN_SET(cuntongncxx.NCXX_DS,cid)>0;
END WHILE;
return str;
END