postgresql递归查询

postgresql 递归查询

如果数据库中表中的记录存在这单继承关系，即每条记录只有一条父亲记录，要查询这条记录的所有的子记录，可以使用递归查询来简化查询复杂度。

有如下的表关系和记录，存放各个法院的ID，名称和它的父级单位ID。

CREATE TABLE "db_uim"."t_aty_corp" (
"c_id" varchar(50) COLLATE "default" NOT NULL,
"c_name" varchar(150) COLLATE "default" NOT NULL,
"c_pid" varchar(50) COLLATE "default"
);

COMMENT ON TABLE "db_uim"."t_aty_corp" IS '单位表';

COMMENT ON COLUMN "db_uim"."t_aty_corp"."c_id" IS '主键';

COMMENT ON COLUMN "db_uim"."t_aty_corp"."c_name" IS '单位名称';

COMMENT ON COLUMN "db_uim"."t_aty_corp"."c_pid" IS '父代码';


INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('0', '最高人民法院', '-1');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('1', '北京市高级人民法院', '0');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('10', '北京市大兴区人民法院', '1');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('100', '河北省高级人民法院', '0');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('126', '河北省唐山市中级人民法院', '100');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('129', '唐山市古冶区人民法院', '126');

递归分为两种：向下递归和向上递归

向下递归

查询北京高院和所管辖的法院单位：

WITH RECURSIVE cp AS (
        SELECT
            corp1.*
        FROM
            db_uim.t_aty_corp corp1
        WHERE
            corp1.c_id = '1'
        UNION ALL
            SELECT
                corp2.*
            FROM
                db_uim.t_aty_corp corp2,
                cp
            WHERE
                corp2.c_pid = cp.c_id
    ) SELECT cp.c_id,cp.c_name,cp.c_pid FROM cp;

查询结果如下：

向上递归

查询唐山市古冶区人民法院和上级单位：

WITH RECURSIVE cp AS (
        SELECT
            corp1.*
        FROM
            db_uim.t_aty_corp corp1
        WHERE
            corp1.c_id = '129'
        UNION ALL
            SELECT
                corp2.*
            FROM
                db_uim.t_aty_corp corp2,
                cp
            WHERE
                corp2.c_id = cp.c_pid
    ) SELECT cp.c_id,cp.c_name,cp.c_pid FROM cp;

查询结果如下：