postgresql中在父子关系表(包含parent_id)中查询子代及父代数据

一、根据areaCode查的所有子代数据

/**
     * d_city表树形父子关系数据结构，根据area_code递归查询后代节点
     * @param areaCode 区域编码
     * @return List<DCityDO> 父子层级城市列表
     */
    @Select("WITH RECURSIVE P AS (\n" +
            "    SELECT\n" +
            "        t1.ID,\n" +
            "        t1.area_code,\n" +
            "        t1.NAME,\n" +
            "        t1.parent_code,\n" +
            "        t1.LEVEL \n" +
            "    FROM\n" +
            "        d_city t1 \n" +
            "    WHERE\n" +
            "        t1.area_code = #{areaCode} UNION ALL\n" +
            "    SELECT\n" +
            "        t2.ID,\n" +
            "        t2.area_code,\n" +
            "        t2.NAME,\n" +
            "        t2.parent_code,\n" +
            "        t2.LEVEL \n" +
            "    FROM\n" +
            "        d_city t2\n" +
            "        INNER JOIN P ON t2.parent_code = P.area_code \n" +
            "    WHERE\n" +
            "        t2.LEVEL IN ( '1', '2', '0' ) \n" +
            "    ) SELECT ID\n" +
            "    ,\n" +
            "    area_code,\n" +
            "    NAME,\n" +
            "    parent_code ,\n" +
            "    LEVEL\n" +
            "FROM\n" +
            "P ORDER BY LEVEL")
    List<DCityDO> selectPosterityListByCityCode(@Param("areaCode") String areaCode);

二、根据areaCode查的所有父代数据

@Select("WITH RECURSIVE P AS (\n" +
            "    SELECT\n" +
            "        t1.ID,\n" +
            "        t1.area_code,\n" +
            "        t1.NAME,\n" +
            "        t1.parent_code,\n" +
            "        t1.LEVEL \n" +
            "    FROM\n" +
            "        d_city t1 \n" +
            "    WHERE\n" +
            "        t1.area_code = #{areaCode} UNION ALL\n" +
            "    SELECT\n" +
            "        t2.ID,\n" +
            "        t2.area_code,\n" +
            "        t2.NAME,\n" +
            "        t2.parent_code,\n" +
            "        t2.LEVEL \n" +
            "    FROM\n" +
            "        d_city t2\n" +
            "        INNER JOIN P ON t2.area_code = P.parent_code \n" +
            "    WHERE\n" +
            "        t2.LEVEL IN ( '1', '2', '0' ) \n" +
            "    ) SELECT ID\n" +
            "    ,\n" +
            "    area_code,\n" +
            "    NAME,\n" +
            "    parent_code ,\n" +
            "    LEVEL\n" +
            "FROM\n" +
            "P ORDER BY LEVEL")
    List<DCityDO> selectAncestorListByCityCode(@Param("areaCode") String areaCode);

三、父子数据表结构

CREATE TABLE "public"."d_city" (
  "id" int4 NOT NULL,
  "create_date" timestamp(6) DEFAULT now(),
  "modify_date" timestamp(6) DEFAULT now(),
  "version" varchar(50) COLLATE "pg_catalog"."default",
  "level" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "area_code" varchar COLLATE "pg_catalog"."default",
  "name" varchar(50) COLLATE "pg_catalog"."default",
  "short_name" varchar(50) COLLATE "pg_catalog"."default",
  "merger_name" varchar(50) COLLATE "pg_catalog"."default",
  "parent_code" varchar(50) COLLATE "pg_catalog"."default",
  "latitude" float8,
  "longitude" float8,
  "pinyin" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "dept_id" int4 DEFAULT '-999'::integer,
  "user_id" int4 DEFAULT '-999'::integer,
  CONSTRAINT "d_city_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "uniq_area_code" UNIQUE ("area_code")
)
;

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

CREATE UNIQUE INDEX "d_city_area_code_idx" ON "public"."d_city" USING btree (
  "area_code" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "d_city_level_idx" ON "public"."d_city" USING btree (
  "level" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "d_city_naem_idx" ON "public"."d_city" USING btree (
  "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST,
  "merger_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "idx_name_level" ON "public"."d_city" USING btree (
  "level" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST,
  "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

COMMENT ON COLUMN "public"."d_city"."id" IS '主键';

COMMENT ON COLUMN "public"."d_city"."create_date" IS '创建时间';

COMMENT ON COLUMN "public"."d_city"."modify_date" IS '更新时间';

COMMENT ON COLUMN "public"."d_city"."version" IS '版本';

COMMENT ON COLUMN "public"."d_city"."level" IS '级别';

COMMENT ON COLUMN "public"."d_city"."area_code" IS '区域编码';

COMMENT ON COLUMN "public"."d_city"."name" IS '区域名称';

COMMENT ON COLUMN "public"."d_city"."short_name" IS '区域简称';

COMMENT ON COLUMN "public"."d_city"."merger_name" IS '组织全程';

COMMENT ON COLUMN "public"."d_city"."parent_code" IS '父类编码';

COMMENT ON COLUMN "public"."d_city"."latitude" IS '纬度';

COMMENT ON COLUMN "public"."d_city"."longitude" IS '经度';

COMMENT ON COLUMN "public"."d_city"."pinyin" IS '中文拼音';

COMMENT ON COLUMN "public"."d_city"."dept_id" IS '所属部门';

COMMENT ON COLUMN "public"."d_city"."user_id" IS '所属用户';

COMMENT ON CONSTRAINT "uniq_area_code" ON "public"."d_city" IS '区域编码唯一索引';

COMMENT ON TABLE "public"."d_city" IS '城市信息';