SqlServer的案例

with area(id,"name",f_id,leve) as (

  select  1,'中国',0,1 union all

  select  2,'湖北',1,2 union all

  select  3,'武汉',2,3 union all

  select  4,'云贵',1,2 union all

  select  5,'云南',4,3 union all

  select  6,'贵阳',4,3 union all

  select  7,'云南子区',5,4 union all

  select  8,'贵阳子区',6,4 union all

  select  9,'蔡甸',2,3

), "table"(id,area_id,"money") as (

  select  1,3,10 union all

  select  2,9,5 union all

  select  3,7,20 union all

  select  4,8,30

)

--使用cte递归求出每个节点的路径

,t(id,f_id,"name","level",fullpath) as (

  select a.id,a.f_id,a."name",a.leve,cast(a.id as varchar(max))

  from area a

  where a.leve=1

  union all

  select b.id,b.f_id,b."name",b.leve,t.fullpath+'->'+cast(b.id as varchar(max))

  from area b

  inner join t on t.id=b.f_id

)

你这个是父子节点递归查询了

不同数据库不一样

Oracle    https://www.jb51.net/article/132822.htm

mysql     https://blog.csdn.net/qq_16992475/article/details/121783805

sql server   https://blog.csdn.net/weixin_34221276/article/details/92054454