mysql 递归查询父节点及子节点

最新推荐文章于 2025-10-17 15:55:23 发布

原创最新推荐文章于 2025-10-17 15:55:23 发布 · 1.8k 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一种使用SQL函数查询数据库中指定节点的父节点及子节点的方法，包括不包含本节点和包含本节点的子节点查询。通过创建自定义SQL函数`getParentList`和`getChildList`实现了对树状结构数据的高效检索。

查询父节点

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