yanhechao 2021-12-28 16:10:54

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`

评论

首页 - Wiki
Copyright © 2011-2024 iteam. Current version is 2.129.0. UTC+08:00, 2024-06-24 02:30
浙ICP备14020137号-1 $访客地图$