SQL递归技巧

1.读样例

with recursive cet_dpt(id, parent_id, path, org_category, level,depart_name) as (select id ,
                                                                            parent_id,
                                                                            depart_name as path,
                                                                            org_category,
                                                                            1           as level,
                                                                            sd.depart_name
                                                                     from isolarerp.sys_depart sd
                                                                     where del_flag = 0
                                                                       and sd.org_code = 'A09B15'
                                                                     union all
                                                                     select t.id,
                                                                            t.parent_id,
                                                                            concat(c.path, '->', t.depart_name) as path,
                                                                            t.org_category,
                                                                            level + 1,
                                                                            t.depart_name
                                                                     from isolarerp.sys_depart t
                                                                              inner join cet_dpt c on t.parent_id = c.id
                                                                     where c.level<7
                                                                     )
select *![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/21294bbb29624776ad650f73a625514e.png)

from cet_dpt

2.解释样例

sd是top点t是top下邻点c是sd的化身，初始的c就是sd。