数据库:oceanbase-ce(MySQL推荐在8以上,5.7会报错)
1、递归sql主体(知道父,找全部子):
WITH RECURSIVE country_area_tree AS (
-- 非递归部分,初始化查询
SELECT id, area_name, parent_id, 0 AS level
FROM country_area
WHERE id = '589004044419077'
UNION ALL
-- 递归部分,找到子节点
SELECT l.id, l.area_name, l.parent_id, lt.level + 1
FROM country_area l
JOIN country_area_tree lt ON l.parent_id = lt.id
)
SELECT
id,
area_name,
parent_id,
level
FROM country_area_tree
ORDER BY level, id;
测试数据(为“心知天气”获取的城市信息,未加载全球城市信息):
Conmi的正确答案-MySQL的层级递归查询(递归公共表表达式,CTE)测试数据
默认显示:
查询后:
2、递归sql主体(知道子,找全部父):
WITH RECURSIVE hierarchy AS (
-- 初始查询部分
SELECT
id, area_name, parent_id, 1 AS depth
FROM country_area
WHERE id = '590175356940297'
UNION ALL
-- 递归部分
SELECT t.id, t.area_name, t.parent_id, h.depth + 1 AS depth
FROM country_area t
JOIN hierarchy h ON t.id = h.parent_id
)
SELECT id, area_name, parent_id, depth
FROM hierarchy
ORDER BY depth DESC;