目录
一、创建表结构和插入数据
二、查询所有子节点
三、查询所有父节点
四、查询指定节点的根节点
五、查询所有兄弟节点（同级节点）
六、获取祖先节点及其所有子节点
七、查询每个节点之间的层级关系
八、查询指定节点之间的层级关系
一、创建表结构和插入数据
CREATE TABLE `region` ( `id` VARCHAR(36) NOT NULL DEFAULT (UUID()) COMMENT '主键', `parent_id` VARCHAR(36) COMMENT '父键', `name` VARCHAR(255) NOT NULL COMMENT '地区名', `latitude` DECIMAL(10, 6) COMMENT '经度', `longitude` DECIMAL(10, 6) COMMENT '纬度', PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (NULL, '江苏省', 31.2304, 120.663); SET @jiangsu_id = (SELECT `id` FROM `region` WHERE `name` = '江苏省'); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@jiangsu_id, '苏州市', 31.2988, 120.5853); SET @suzhou_id = (SELECT `id` FROM `region` WHERE `name` = '苏州市'); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@suzhou_id, '张家港市', 31.8754, 120.5553), (@suzhou_id, '吴中区', 31.2622, 120.6446), (@suzhou_id, '相城区', 31.3697, 120.646), (@suzhou_id, '吴江区', 31.1791, 120.6411); SET @zhangjiagang_id = (SELECT `id` FROM `region` WHERE `name` = '张家港市');
SET @wuzhong_id = (SELECT `id` FROM `region` WHERE `name` = '吴中区');
SET @xiangcheng_id = (SELECT `id` FROM `region` WHERE `name` = '相城区');
SET @wujiang_id = (SELECT `id` FROM `region` WHERE `name` = '吴江区'); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@zhangjiagang_id, '凤凰镇', 31.8754, 120.5553), (@zhangjiagang_id, '塘桥镇', 31.8754, 120.5553); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@wuzhong_id, '木渎镇', 31.2622, 120.6446); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@xiangcheng_id, '黄埭镇', 31.3697, 120.646); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@wujiang_id, '平望镇', 31.1791, 120.6411), (@wujiang_id, '黎里镇', 31.1791, 120.6411); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@jiangsu_id, '无锡市', 31.5704, 120.3055); SET @wuxi_id = (SELECT `id` FROM `region` WHERE `name` = '无锡市'); INSERT INTO `region` (`parent_id`, `name`, `latitude`, `longitude`)
VALUES (@wuxi_id, '锡山区', 31.5887, 120.3573), (@wuxi_id, '惠山区', 31.6514, 120.3036), (@wuxi_id, '滨湖区', 31.5502, 120.2598), (@wuxi_id, '江阴市', 31.9086, 120.2855), (@wuxi_id, '宜兴市', 31.3623, 119.8233);二、查询所有子节点
从指定的父节点开始，递归查找所有子级地区：
WITH RECURSIVE region_cte AS ( SELECT id, name, parent_id FROM region WHERE name = '苏州市' -- 根据指定父节点开始 UNION ALL SELECT r.id, r.name, r.parent_id FROM region r JOIN region_cte c ON r.parent_id = c.id -- 递归查找所有子地区
)
SELECT * FROM region_cte查询结果：
三、查询所有父节点
从指定的子节点开始，递归查找所有父级地区：
WITH RECURSIVE region_cte AS ( SELECT id, name, parent_id FROM region WHERE name = '张家港市' -- 从指定子节点开始 UNION ALL SELECT r.id, r.name, r.parent_id FROM region r JOIN region_cte c ON r.id = c.parent_id -- 递归查找父级区域
)
SELECT * FROM region_cte查询结果：
四、查询指定节点的根节点
可以通过递归查找父节点，最终筛选出根节点（即没有父节点的）：
WITH RECURSIVE region_cte AS ( SELECT id, name, parent_id FROM region WHERE name = '张家港市' -- 从指定节点开始 UNION ALL SELECT r.id, r.name, r.parent_id FROM region r JOIN region_cte c ON r.id = c.parent_id -- 递归查找父级区域
)
SELECT * FROM region_cte
WHERE parent_id IS NULL -- 筛选根节点查询结果：
五、查询所有兄弟节点（同级节点）
查找与指定节点同级的所有区域：
SELECT id, name, parent_id
FROM region
WHERE parent_id = (SELECT parent_id FROM region WHERE name = '张家港市') -- 获取同级父节点
AND name != '张家港市' -- 排除自身查询结果：
六、获取祖先节点及其所有子节点
获取指定节点的祖先以及每个祖先的所有子节点：
WITH RECURSIVE region_ancestors AS ( SELECT id, name, parent_id FROM region WHERE name = '张家港市' -- 从指定节点开始查找祖先 UNION ALL SELECT r.id, r.name, r.parent_id FROM region r JOIN region_ancestors c ON r.id = c.parent_id -- 递归查找所有父级区域
),
region_children AS ( SELECT id, name, parent_id FROM region UNION ALL SELECT r.id, r.name, r.parent_id FROM region r JOIN region_children c ON r.parent_id = c.id -- 递归查找所有子级区域
)
SELECT * FROM region_ancestors
UNION
SELECT * FROM region_children
WHERE parent_id IN (SELECT id FROM region_ancestors) -- 获取所有祖先的子节点查询结果：
七、查询每个节点之间的层级关系
返回每个地区的详细信息，包括其层级和路径：
WITH RECURSIVE region_tree (id, name, parent_id, level, path) AS ( -- 选择根节点，即没有父节点的区域 SELECT id, name, parent_id, 1 AS level, CAST(name AS CHAR(200)) AS path FROM region WHERE parent_id IS NULL UNION ALL -- 递归查找子区域 SELECT r.id, r.name, r.parent_id, rt.level + 1 AS level, CONCAT(rt.path, ' -> ', r.name) AS path FROM region r JOIN region_tree rt ON r.parent_id = rt.id
)
-- 查询结果
SELECT id, name, parent_id, level, path
FROM region_tree
ORDER BY id -- 根据需要排序查询结果：
八、查询指定节点之间的层级关系
假设获取苏州市（或其他特定节点）的所有子节点及其层级结构
WITH RECURSIVE region_tree (id, name, parent_id, level, path) AS ( -- 选择指定节点作为根节点 SELECT id, name, parent_id, 1 AS level, CAST(name AS CHAR(200)) AS path FROM region WHERE name = '苏州市' -- 替换为你想要查询的节点名称 UNION ALL -- 递归查找子区域 SELECT r.id, r.name, r.parent_id, rt.level + 1 AS level, CONCAT(rt.path, ' -> ', r.name) AS path FROM region r JOIN region_tree rt ON r.parent_id = rt.id
)
-- 查询结果
SELECT id, name, parent_id, level, path
FROM region_tree
ORDER BY level, id -- 根据层级和 ID 排序查询结果：
