pgsql实现递归查询

 1、子查父

with recursive cte as(
    select
        // todo 查询参数选择，子父需对应
        *,
        // 深度赋值，名称拼接写法类似
        1 as depth
        from
        biz_info
    where deleted = 0
        and id = #{id}
    union all
    select
        // todo 查询参数选择，子父需对应
        parent.*,
        child.depth + 1
    from
        biz_info parent
    inner join cte child on parent.id = child.parent_id
    where
        parent.deleted = 0
        // 最大递归深度，防止死循环
        and child.depth < #{maxDepth}
)
select * from cte

2、父查子

WITH RECURSIVE cte AS (
    SELECT
        // todo 查询参数选择，子父需对应
        *,
        // 深度赋值，名称拼接写法类似
        1 AS depth
    FROM biz_info
    WHERE deleted = 0
        AND id = #{id}
    union all
    SELECT
        // todo 查询参数选择，子父需对应
        child.*,
        parent.depth + 1
    FROM biz_info child
    INNER JOIN cte parent ON child.parent_id = parent.id
    WHERE
        child.deleted = 0
        // 最大递归深度，防止死循环
        AND parent.depth < #{maxDepth}
)
SELECT * FROM cte
  

RECURSIVE是pgsql提供的递归方法，可以通过id、parent_id自连接以查询出所有的树形节点，应用于部门树、仓库树等等业务场景