2024-06-20 PostgreSQL 之递归查询

PostgreSQL 之递归查询树状层次结构表

PostgreSQL 之递归查询树状层次结构表

PostgreSQL 的 WITH 查询有一个特殊的选项 RECURSIVE，他可以引用自身的输出，从而实现递归。递归查询通常用于处理层次或者树状结构的数据。

CREATE TABLE public.tree_data (
 id bigserial,
 tree_code VARCHAR(20),
 tree_name VARCHAR(20),
 tree_level INT8,
 tree_pcode VARCHAR(20),
 tree_sort INT8,
 tree_state INT8,
 create_time TIMESTAMP(0),
 create_userid INT8,
 create_user VARCHAR(20),
 update_time TIMESTAMP(0),
 update_userid INT8,
 update_user VARCHAR(20));
 
 COMMENT ON COLUMN tree_data.id IS '自增主键';
 COMMENT ON COLUMN tree_data.tree_code IS '编码';
 COMMENT ON COLUMN tree_data.tree_name IS '名称';
 COMMENT ON COLUMN tree_data.tree_level IS '层级ID';
 COMMENT ON COLUMN tree_data.tree_pcode IS '父编码';
 COMMENT ON COLUMN tree_data.tree_sort IS '排序';
 COMMENT ON COLUMN tree_data.tree_state IS '状态';
 COMMENT ON COLUMN tree_data.create_time IS '创建时间';
 COMMENT ON COLUMN tree_data.create_userid IS '创建人ID';
 COMMENT ON COLUMN tree_data.create_user IS '创建人';
 COMMENT ON COLUMN tree_data.update_time IS '更新时间';
 COMMENT ON COLUMN tree_data.update_userid IS '更新人ID';
 COMMENT ON COLUMN tree_data.update_user IS '更新人';
 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('001', '中国', 0, NULL, 1, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('002', '陕西', 1, '001', 1, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('003', '四川', 1, '001', 2, 1);
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('004', '西安', 2, '002', 1, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('005', '咸阳', 2, '002', 2, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('006', '榆林', 2, '002', 3, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('007', '成都', 2, '003', 1, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('008', '绵阳', 2, '003', 2, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('009', '雁塔区', 3, '004', 1, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('010', '高新区', 3, '004', 2, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('011', '灞桥区', 3, '004', 3, 1); 
INSERT INTO public.tree_data(tree_code,tree_name,tree_level,tree_pcode,tree_sort,tree_state) VALUES('012', '武侯区', 3, '007', 1, 1);

UPDATE public.tree_data SET create_time=NOW()::TIMESTAMP(0),create_userid=1001,create_user='创建人',update_time=NOW()::TIMESTAMP(0),update_userid=1002,update_user='修改人'; 

需求：给出一个 tree_code 找出他所在的省份
WITH RECURSIVE parent_tree_data(tree_code, tree_name, tree_pcode, tree_level, tree_state, create_time) AS (
SELECT tree_code, tree_name, tree_pcode, tree_level, tree_state, create_time
FROM public.tree_data 
WHERE tree_code = '012' 
UNION ALL 
SELECT td.tree_code, td.tree_name, td.tree_pcode, td.tree_level, td.tree_state, td.create_time
FROM public.tree_data AS td 
INNER JOIN parent_tree_data AS ptd ON td.tree_code = ptd.tree_pcode)

SELECT * FROM parent_tree_data WHERE tree_level = 1;
SELECT * FROM parent_tree_data WHERE tree_level = 2;