postgreSQL 递归查询，使用数组

前言

递归查询使用with recursive

数组使用ARRAY

运用场景：用于统计多个层级中某一个层级以及下面所有分类的数据

内容

表结构：

DROP TABLE IF EXISTS "public"."test_tree";
CREATE TABLE "public"."test_tree" (
  "id" int4 NOT NULL,
  "code" varchar(50) COLLATE "pg_catalog"."default",
  "name" varchar(128) COLLATE "pg_catalog"."default",
  "pid" int4
)
;
COMMENT ON COLUMN "public"."test_tree"."code" IS '编码';
COMMENT ON COLUMN "public"."test_tree"."name" IS '名称';
COMMENT ON COLUMN "public"."test_tree"."pid" IS '上级ID';
COMMENT ON TABLE "public"."test_tree" IS '测试表';

-- ----------------------------
-- Records of test_tree
-- ----------------------------
INSERT INTO "public"."test_tree" VALUES (1, '1', '动物', 0);
INSERT INTO "public"."test_tree" VALUES (2, '02000', '猫', 1);
INSERT INTO "public"."test_tree" VALUES (3, '03000', '狗', 1);
INSERT INTO "public"."test_tree" VALUES (4, '01000', '猪', 1);
INSERT INTO "public"."test_tree" VALUES (10, '02001', '英短', 2);
INSERT INTO "public"."test_tree" VALUES (11, '02002', '美短', 2);
INSERT INTO "public"."test_tree" VALUES (12, '02003', '橘猫', 2);
INSERT INTO "public"."test_tree" VALUES (13, '02004', '布偶', 2);
INSERT INTO "public"."test_tree" VALUES (14, '02005', '三花', 2);
INSERT INTO "public"."test_tree" VALUES (15, '03001', '萨摩', 3);
INSERT INTO "public"."test_tree" VALUES (16, '03002', '藏獒', 3);
INSERT INTO "public"."test_tree" VALUES (17, '03003', '博美', 3);
INSERT INTO "public"."test_tree" VALUES (18, '03004', '泰迪', 3);
INSERT INTO "public"."test_tree" VALUES (19, '03005', '二哈', 3);
INSERT INTO "public"."test_tree" VALUES (5, '01001', '红猪', 4);
INSERT INTO "public"."test_tree" VALUES (6, '01002', '绿猪', 4);
INSERT INTO "public"."test_tree" VALUES (7, '01003', '蓝猪', 4);
INSERT INTO "public"."test_tree" VALUES (8, '01004', '黄猪', 4);
INSERT INTO "public"."test_tree" VALUES (9, '01005', '紫猪', 4);

-- ----------------------------
-- Primary Key structure for table test_tree
-- ----------------------------
ALTER TABLE "public"."test_tree" ADD CONSTRAINT "test_tree_pkey1" PRIMARY KEY ("id");

查询sql：(本例子设置了3个层级，实际运用中层级不做限制，不受影响)

WITH RECURSIVE le ( id, code, NAME, pid, PATH, LEVEL ) AS (
	SELECT 
        id,
		code,
		NAME,
		pid,
		ARRAY [ ID ] AS PATH,
		1 AS LEVEL 
	FROM
		test_tree 
	WHERE
		pid IN ( 0 ) UNION ALL
	SELECT
		e2.id,
		e2.code,
		e2.NAME,
		e2.pid,
		e3.PATH || e3.ID,
		e3.LEVEL + 1 
	FROM
		test_tree e2,
		le e3 
	WHERE
		e3.id= e2.pid 
	) SELECT
	* 
FROM
	le 
ORDER BY
	rpad( LEVEL :: VARCHAR, 5, '0' ) ASC

结果如下：

path列是返回的级层数组，通过path[1]、path[2]等方式可以取出对应值