一、概述
相信大家在实际的开发工程中，都会遇到需要依据当前节点，查询出其上级节点或下级节点的需求。下面就我在工作过程中的处理方式记录如下，如有片面之处，欢迎批评指正。
二、示例表结构
初始表数据如图：
查看表结构和初始数据脚本
DROP TABLE IF EXISTS `t_cfg_region`;
CREATE TABLE `t_cfg_region` (
`region_id` bigint NOT NULL AUTO_INCREMENT COMMENT '空间编号',
`region_name` varchar(100) NOT NULL COMMENT '空间名称',
`up_region_id` bigint NOT NULL COMMENT '父空间编号',
`region_level` tinyint NOT NULL COMMENT '级别',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '更新时间',
`remark` varchar(512) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`region_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24764 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_cfg_region
-- ----------------------------
INSERT INTO `t_cfg_region` VALUES ('24741', '江苏省', '0', '1', '2023-04-25 09:30:42', '2023-04-25 09:30:42', null);
INSERT INTO `t_cfg_region` VALUES ('24742', '南京', '24741', '2', '2023-04-25 09:34:54', '2023-04-25 09:34:54', null);
INSERT INTO `t_cfg_region` VALUES ('24743', '苏州', '24741', '2', '2023-04-25 09:35:16', '2023-04-25 09:35:16', null);
INSERT INTO `t_cfg_region` VALUES ('24744', '秦淮区', '24742', '3', '2023-04-25 09:37:13', '2023-04-25 09:37:13', null);
INSERT INTO `t_cfg_region` VALUES ('24745', '吴中区', '24743', '3', '2023-04-25 09:38:00', '2023-04-25 09:38:00', null);
三、查父节点
通过秦淮区查询父节点
select
tmpId as region_id
from
(
SELECT t.* from (
select
@r as tmpId,
(select @r := up_region_id from t_cfg_region where region_id = tmpId) as parentId
from
(select @r := 24744,@l := 0) tp,t_cfg_region cg
where @r != 0
) t where t.parentId is not NULL
) tb;
四、查子节点
查询南京的下级节点
select r.region_id,r.region_name,r.up_region_id,r.region_level
from
t_cfg_region as r
inner join
(
SELECT region_id FROM
(
SELECT t1.region_id,t1.up_region_id,
IF (find_in_set(up_region_id, @pids) > 0,@pids := concat(@pids, ',', region_id),0) AS ischild
FROM
(
SELECT region_id,up_region_id FROM t_cfg_region t ORDER BY up_region_id,region_id
) t1,
(
SELECT
@pids := '24741'
) t2
) t3
WHERE
ischild != 0
union all
select region_id from t_cfg_region where region_id = '24741'
) as tp on r.region_id = tp.region_id