mysql8 递归子查询实现

说明：sql中with xxxx as () 是对一个查询子句做别名，同时数据库会对该子句生成临时表；
with recursive 则是一个递归的查询子句，他会把查询出来的结果再次代入到查询子句中继续查询，如下面的语句

with recursive t1 as (   
  select * from sys_dept  where dept_leader = 1 and delete_flag = 0    
  union all    
  select t.* from sys_dept t inner join t1 on t1.dept_id = t.parent_id and t.delete_flag = 0
) 
select * from t1;

利用with recursive 查询实现向上递归

with recursive type_cte as (
    select id,name ,parent_id  from province  where id = 46
    union all
    select t.id,concat(type_cte2.name,'>',t.name),t.parent_id
    from province t
    inner join type_cte type_cte2 on t.id  = type_cte2.parent_id
)
select
    id, name, parent_id
from type_cte;