SQL使用技巧(3.1)递归层次查询Postgresql

场景描述

场景一：员工信息表，根据某一员工编号查找其上级及上级的上级，直至最高权力者；或者下级及下级的下级，直至其管理下的最基层员工。
场景二：机构树，查询某一机构所对应的上级机构，直至最高机构；或者下级机构直至最末级机构
场景三：号码更换（银行卡换号不换卡），给出某一号码，查找该卡历史所有换号信息，给出的号码可能是历史的、也可能是最新的。
…

一.递归方法 WITH RECURSIVE

注意：用于递归判断的字段必须是integer整数型，当字段建成varchar字符串时，递归会报错——“操作符不存在: character varying = integer”

1.1创建表和数据

drop table if exists tree_tb;
create table tree_tb (
	employee int
	,leader int
	,level varchar(10)
	)
;

insert into tree_tb values(1001,1005,'L1'),
	(1002,1005,'L1'),
	(1003,1005,'L1'),
	(1004,1005,'L1'),
	(1005,1008,'L2'),
	(1006,1009,'L2'),
	(1007,1006,'L1'),
	(1008,1010,'L3'),
	(1009,1010,'L3'),
	(1010,null,'L4')
	;

select * from tree_tb;

1.2构建递归查询

-- 这里 UNION ALL 和 UNION 结果一样
WITH RECURSIVE t1 AS (
SELECT employee, leader, level, 1::integer recursive_nm
  FROM tree_tb
  WHERE employee=1005
--UNION ALL
--  SELECT t2.employee, t2.leader, t2.level, recursive_nm +1
--  FROM tree_tb t2
--  JOIN t1 ON t1.employee = t2.leader
UNION ALL
  SELECT t3.employee, t3.leader, t3.level, recursive_nm +1
  FROM tree_tb t3
  JOIN t1 ON t1.leader = t3.employee
  -- where t1.recursive_nm < 2 -- 可以控制递归次数
  )
SELECT * FROM t1;

结论先行：如下图结果，employee=1005的leader=1008，leader=1008的leader=1010，这里通过2次递归完成查询。

方法解释

-- 第一步：查询employee=1005
SELECT employee, leader, level
FROM tree_tb WHERE employee=1005
-- 第1步返回查询结果
employee	leader	level
1,005	1,008	L2
-- 第1步返回t1结果
employee	leader	level  recursive_nm
1,005	1,008	L2  1

-- 第二步：查询t1.leader = t3.employee，即查询employee=1008
SELECT employee, leader, level
FROM tree_tb WHERE employee=1008
-- 第2步返回结果
employee	leader	level
1,008	1,010	L3
-- 第2步返回t1结果
employee	leader	level  recursive_nm
1,005	1,008	L2  1
1,008	1,010	L3	2

-- 第三步：查询t1.leader = t3.employee，即查询employee in (1008,1010)
SELECT employee, leader, level
FROM tree_tb WHERE employee in (1008,1010)
-- 第3步返回结果
employee	leader	level
1,008	1,010	L3
1,010	[NULL]	L4
-- 第3步返回t1结果
employee	leader	level  recursive_nm
1,005	1,008	L2  1
1,008	1,010	L3	2
1,010	[NULL]	L4	3

反向递归查询t1.employee=t2.leader的同理，只是更换了匹配关系，这里做了1次递归 t2.leader=1005 便终止了。

WITH RECURSIVE t1 AS (
SELECT employee, leader, level, 1::integer recursive_nm
  FROM tree_tb
  WHERE employee=1005
UNION ALL
  SELECT t2.employee, t2.leader, t2.level, recursive_nm +1
  FROM tree_tb t2
  JOIN t1 ON t1.employee = t2.leader
--UNION ALL
--  SELECT t3.employee, t3.leader, t3.level, recursive_nm +1
--  FROM tree_tb t3
--  JOIN t1 ON t1.leader = t3.employee
  )
SELECT * FROM t1;

二.存储过程传参递归查询

存储过程包括删除旧表、建立新表、插入正向数据、插入反向数据

CREATE OR REPLACE FUNCTION recursive_slt(source_int integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
	DECLARE V_STEP VARCHAR(10) default '0';

	begin
		
		V_STEP:='1'; -- 删除表
		drop table if exists recursive_result;

		V_STEP:='2'; -- 建立表
		create table recursive_result(
			employee int
			,leader int
			,level varchar(10)
			,recursive_nm int
			)
		;

		V_STEP:='3'; -- 插入数据
		WITH RECURSIVE t1 AS (
		SELECT employee, leader, level, 1::integer recursive_nm
		  FROM tree_tb
		  WHERE employee=source_int
		--UNION ALL
		--  SELECT t2.employee, t2.leader, t2.level, recursive_nm +1
		--  FROM tree_tb t2
		--  JOIN t1 ON t1.employee = t2.leader
		UNION ALL
		  SELECT t3.employee, t3.leader, t3.level, recursive_nm +1
		  FROM tree_tb t3
		  JOIN t1 ON t1.leader = t3.employee
--		  where t1.recursive_nm < 2
		  )
		insert into recursive_result
		SELECT * FROM t1;

		WITH RECURSIVE t1 AS (
		SELECT employee, leader, level, 1::integer recursive_nm
		  FROM tree_tb
		  WHERE employee=source_int
		UNION ALL
		 SELECT t2.employee, t2.leader, t2.level, recursive_nm +1
		 FROM tree_tb t2
		 JOIN t1 ON t1.employee = t2.leader
		-- UNION ALL
		--   SELECT t3.employee, t3.leader, t3.level, recursive_nm +1
		--   FROM tree_tb t3
		--   JOIN t1 ON t1.leader = t3.employee
		--   where t1.recursive_nm < 2
		  )
		insert into recursive_result
		SELECT * FROM t1;
			
		return 0;
	END;
$function$
;

Postgres执行存储过程和获取递归信息如下：

-- 执行存储过程
select recursive_slt(1005);
-- 查看执行结果
select distinct * from recursive_result;

三.帆软Report配置查询界面

3.1FineReport 连接Postgres

FineReport 连接 Postgres方法见 FineReport导入Excel数据的完整示例，不再赘述

3.2构建数据集

界面左侧点击“+”，选择“数据库查询”

填写递归查询语句，构建参数 ‘${svalue}’

3.3构建cpt报表

帆软报告页面的设计构建见 FineReport帆软报表使用入门，不再赘述

3.4前台页面浏览

通过输入要查找的编号，查询出 lead=employee 向上递归，employee=lead 向下递归同理，不再赘述

---

声明：本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考，不构成实际商业建议，如有雷同纯属巧合。