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;
查询结果如下: