Sql技术MySQL8根据parentid生成全路径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`