Sql技术：mysql 8 根据parent_id生成全路径 ancestors
代码演示
create or replace
algorithm = UNDEFINED view `view_dept_ancestors` as with recursive `cte` as (
select
`sys_dept`.`dept_id` as `dept_id`,
`sys_dept`.`parent_id` as `parent_id`,
1 as `lvl`
from
`sys_dept`
union all
select
`c`.`dept_id` as `dept_id`,
`t`.`parent_id` as `parent_id`,
(`c`.`lvl` + 1) as `lvl + 1`
from
(`cte` `c`
join `sys_dept` `t` on
((`t`.`dept_id` = `c`.`parent_id`))))
select
`cte`.`dept_id` as `dept_id`,
group_concat(`cte`.`parent_id` order by `cte`.`lvl` desc separator ',') as `ancestors`
from
`cte`
group by
`cte`.`dept_id`
