postgresql 遞歸查詢上下級節(jié)點樹

wwq圖書世界 2022-06-17 發(fā)布于山東

展開全文

1、表結(jié)構如下

CREATE TABLE "public"."city" (
  "id" int4 NOT NULL DEFAULT nextval('city_id_seq'::regclass),
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "parent_id" int4
)
;

ALTER TABLE "public"."city" OWNER TO "postgres";

COMMENT ON COLUMN "public"."city"."id" IS '主鍵';

COMMENT ON COLUMN "public"."city"."name" IS '地區(qū)';

COMMENT ON COLUMN "public"."city"."parent_id" IS '主鍵';

2、插入數(shù)據(jù)

INSERT INTO "public"."city" VALUES (1, '中國', NULL);
INSERT INTO "public"."city" VALUES (2, '重慶', 1);
INSERT INTO "public"."city" VALUES (3, '四川', 1);
INSERT INTO "public"."city" VALUES (4, '北京', 1);
INSERT INTO "public"."city" VALUES (5, '渝北區(qū)', 2);
INSERT INTO "public"."city" VALUES (6, '九龍坡區(qū)', 2);
INSERT INTO "public"."city" VALUES (7, '大渡口區(qū)', 2);
INSERT INTO "public"."city" VALUES (8, '廣安', 3);
INSERT INTO "public"."city" VALUES (9, '成都', 3);
INSERT INTO "public"."city" VALUES (10, '德陽', 3);
INSERT INTO "public"."city" VALUES (11, '鄰水縣', 8);
INSERT INTO "public"."city" VALUES (12, '九龍鎮(zhèn)', 11);
INSERT INTO "public"."city" VALUES (13, '御臨鎮(zhèn)', 11);
INSERT INTO "public"."city" VALUES (14, '昌平區(qū)', 4);
INSERT INTO "public"."city" VALUES (15, '懷柔區(qū)', 4);

3、想下遞歸查詢

WITH RECURSIVE r AS (
       SELECT * FROM city WHERE id = 3
     union   ALL
       SELECT city.* FROM city, r WHERE city.parent_id = r.id
     )
SELECT * FROM r ORDER BY id;

結(jié)果如下：
在這里插入圖片描述
4、向上遞歸查詢

WITH RECURSIVE r AS (
       SELECT * FROM city WHERE id = 8
     union   ALL
       SELECT city.* FROM city, r WHERE city.id = r.parent_id
     )
SELECT * FROM r ORDER BY id;

結(jié)果如下：
在這里插入圖片描述