postgresql recursive query

postgresql recursive query

If there is a single inheritance relationship among the records in the database, that is, each record has only one parent record, to query all the child records of this record, recursive query can be used to simplify the query complexity.

There are the following table relationships and records, which store the ID, name and parent unit ID of each court.

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 'Unit table';

COMMENT ON COLUMN "db_uim"."t_aty_corp"."c_id" IS 'Primary key';

COMMENT ON COLUMN "db_uim"."t_aty_corp"."c_name" IS 'Unit name';

COMMENT ON COLUMN "db_uim"."t_aty_corp"."c_pid" IS 'Parent code';


INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('0', 'supreme people 's court', '-1');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('1', 'Beijing Higher People's court', '0');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('10', 'Daxing District People's Court of Beijing Municipality', '1');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('100', 'Hebei Higher People's court', '0');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('126', 'Tangshan intermediate people's Court of Hebei Province', '100');
INSERT INTO "db_uim"."t_aty_corp" ("c_id", "c_name", "c_pid") VALUES ('129', 'Tangshan Guye District People's court', '126');

There are two kinds of recursion: downward recursion and upward recursion

Down recursion

To inquire Beijing High Court and the court unit under its jurisdiction:

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;

The query results are as follows:

Upward recursion

To inquire the people's Court of Guye District of Tangshan City and its superior units:

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;

The query results are as follows:

Tags: PostgreSQL Database

Posted on Thu, 02 Apr 2020 01:34:06 -0700 by TGWSE_GY