pgsql实现部门等树形结构数据的路径信息

最新推荐文章于 2023-10-28 18:41:56 发布

whileLe

最新推荐文章于 2023-10-28 18:41:56 发布

阅读量820

点赞数

分类专栏： java 文章标签：递归查询 SQL WITH RECURSIVE 部门路径组织结构

本文链接：https://blog.csdn.net/sun_dai_le/article/details/126857000

版权

java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

递归查询部门路径信息：

WITH RECURSIVE T (ID, NAME, PARENT_ID, PATH, DEPTH)  AS (
    SELECT ID, NAME, PARENT_ID, id::text AS PATH, 1 AS DEPTH
    FROM org_department
    WHERE PARENT_ID =0

    UNION ALL

    SELECT  D.ID, D.NAME, D.PARENT_ID, concat(T.PATH,'.' ,D.ID::text), T.DEPTH + 1 AS DEPTH
    FROM org_department D
    JOIN T ON D.PARENT_ID = T.ID
    ) SELECT  id,PATH FROM T

parentId为部门的父级id，path为路径信息，以“.”分隔