postgresql递归查找树结构

最新推荐文章于 2024-08-28 11:19:47 发布

蜡笔小新_123

最新推荐文章于 2024-08-28 11:19:47 发布

阅读量823

点赞数 2

文章标签： postgresql java

本文链接：https://blog.csdn.net/weixin_46195267/article/details/130954671

版权

文章展示了如何在PostgreSQL中使用递归查询来实现向上查找父级节点和向下查找所有子级节点的操作，创建了一个名为`demo_tree`的表，包含`id`,`name`,`parent_id`等字段，并插入了相关数据。

摘要由CSDN通过智能技术生成

postgresql递归

在这里插入图片描述

DROP TABLE IF EXISTS "public"."demo_tree";
CREATE TABLE "public"."demo_tree" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(64) COLLATE "pg_catalog"."default",
  "parent_id" varchar(64) COLLATE "pg_catalog"."default",
  "seq" int2,
  "delete_flag" int2 NOT NULL DEFAULT 0
)
;
COMMENT ON COLUMN "public"."demo_tree"."id" IS '主键';
COMMENT ON COLUMN "public"."demo_tree"."name" IS '名称';
COMMENT ON COLUMN "public"."demo_tree"."parent_id" IS '父级节点';
COMMENT ON COLUMN "public"."demo_tree"."seq" IS '排序';
COMMENT ON COLUMN "public"."demo_tree"."delete_flag" IS '是否删除';
COMMENT ON TABLE "public"."demo_tree" IS '树结构';


INSERT INTO "public"."demo_tree" VALUES ('1', '杨幂', '0', 1, 0);
INSERT INTO "public"."demo_tree" VALUES ('3', '杨幂-二级', '1', NULL, 0);
INSERT INTO "public"."demo_tree" VALUES ('4', '赵丽颖-二级', '2', NULL, 0);
INSERT INTO "public"."demo_tree" VALUES ('2', ' 赵丽颖', '0', 2, 0);
INSERT INTO "public"."demo_tree" VALUES ('5', '杨幂-3级', '3', NULL, 0);

向上递归查找父级

	 WITH RECURSIVE cte AS (
            SELECT
                n. ID,
                n. NAME,
                n.parent_id AS pid
            FROM
                demo_tree AS n
            WHERE
                n. id = '5'
            UNION ALL
                SELECT
                    r. ID,
                    r. NAME,
                    r.parent_id AS pid
                FROM
                    cte
                JOIN demo_tree AS r ON cte.pid = r. ID
        ) SELECT * FROM cte

结果
在这里插入图片描述

向下递归查所有子级

WITH RECURSIVE dict AS (
			 SELECT *
			 FROM demo_tree
			 WHERE id='3'
			 union ALL
			 SELECT demo_tree.*
			 FROM demo_tree,
				  dict
			 WHERE demo_tree.parent_id = dict.id
		)
		SELECT id AS id, name as name, parent_id as parentId
		FROM dict
		ORDER BY name