postgresql递归查找树结构

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

结果