基于银行层级结构的递归思路及方法（star with）

周粥粥ph

已于 2022-10-24 10:37:40 修改

阅读量393

点赞数

分类专栏： SQL 文章标签： sql mysql 数据库 1024程序员节

于 2022-10-19 17:59:04 首次发布

本文链接：https://blog.csdn.net/weixin_45875358/article/details/127404252

版权

SQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1、数据表分析

现业务Oracle数据库中有一张机构表PMS_DEPT，示例如下：

DEPT_ID	DEPT_NAME	DEPT_NUM	BRAND_CODE	DEPT_CODE	DEPT_ORG_ID	DEPT_ORG_TYPE	DEPT_PARENT_ID	DEPT_REMARK	DEPT_ISDEL	DEPT_SORT	DEPT_DEPTH	DEPT_PROVINCE_ID	DEPT_CITY_ID	DEPT_AREA_ID	BANK_CODE	DEPT_CREATE_DATE	DEPT_UPDATE_DATE	DEPT_LEVEL	DOT_CODE	AREA_CODE	RN	DEPT_UPDATE_USER
20111420	XX分行	gdgh	gdicbc	001029	2120310	2	1	xx分行	0	29	1	330000	330100	330102	0120100000	1530346860000	1530350241000		1	0000	1201	24

我们需要将该机构表的数据递归成一个含有 该机构及所有上级机构关系 的展平表；

通过观察PMS表数据结构及和对应后端开发人员沟通，我们发现：

表中有两个字段含有层级结构的含义：DEPT_DEPTH、DEPT_LEVEL；（沟通发现DEPT_DEPTH字段正确反映了真实层级结构）；DEPT_DEPTH的01234分别表示：总行、以及分行、二级分行、一级支行、二级支行；
表中DEPT_ID、DEPT_PARENT_ID字段表达了层级的上下关系（父子关系）；
DEPT_CODE自顶向下，每增加一级，字段长度增加3位；

综上所述，我们想到了以下几种思路：

START WITH 自底向上递归；
START WITH 自底向下递归；
通过DEPT_CODE的规律递归；

2、具体方案

2.1 START WITH 基本语法

SELECT ... FROM    + 表名
START WITH         + 条件1    --从哪里开始递归
CONNECT BY PRIOR   + 条件2    --递归的顺序，PRIOR后面跟着什么，就往哪递归，跟着父级就自底向上
WHERE              + 条件3    --递归到哪个位置

条件1：是根节点的限定语句

可以放宽限定条件，以取得多个根节点，也就是多棵树；
在连接关系中，除了可以使用列明外，还允许使用列表达式；
START WITH 子句为可选项，用来标识哪个节点作为查找树形结构的根节点；
若该子句省略，则表示所有满足查询条件的行作为根节点；

条件2：是连接条件
其中用PRIOR表示上一条记录，例如CONNECT BY PRIOR dept_parent_id = dept_id，意思就是上一条记录的dept_parent_id是本条记录的dept_id，即本记录的父亲是上一条记录。
CONNECT BY子句说明每行数据将是按照层次顺序检索，并规定将表中的数据连入树形结构的关系中。
PRIOR运算符必须放置在连接关系的2列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构的顺序是自顶向下，还是自底向上。（PRIOR运算符在哪一侧，就递归到哪一侧）

条件3：是过滤条件，用于对返回的记录进行过滤。

看一个例子：
SELECT * FROM PMS_DEPT
START WITH dept_id = '001'
CONNECT BY PRIOR dept_id = dept_parent_id ;

CONNECT BY PRIOR是结构化查询中用到的；
START WITH... CONNECT BY PRIOR...的作用，简单来说，就是将一个树状结构存储在一张表里。

假如一个表里存在2个字段：dept_parent_id（上级机构ID）、dept_id（机构ID），那么我们可以通过表示每一个机构属于哪一个上级机构，来形成一个树状
结构，通过START WITH... CONNECT BY PRIOR...语法，就可以取得这棵树的所有记录。

--自顶向下以dept_id = '001'这个节点为根节点，向下查询
SELECT * FROM PMS_DEPT
START WITH dept_id = '001'
CONNECT BY PRIOR dept_id = dept_parent_id;

--自底向上以BRH_ID = '0003'这个节点为叶节点，向上查询

SELECT * FROM PMS_DEPT
START WITH dept_id = '001002'
CONNECT BY dept_id = PRIOR dept_parent_id;

2.2 自底向上

我们的需求是含有 该机构及所有上级机构关系 的展平表，所以采用自底向上的逻辑最为直接；

具体SQL如下：

SELECT
    (SELECT max( DEPT_CODE ) FROM pms_dept pd WHERE pd.dept_depth = 0 START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS dept_ZONGHANG,
	(SELECT max( DEPT_CODE ) FROM pms_dept pd WHERE pd.dept_depth = 1 START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS dept_SHENGHANG,
	(SELECT max( DEPT_CODE ) FROM pms_dept pd WHERE pd.dept_depth = 2 START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS dept_SHIHANG,
	(SELECT max( DEPT_CODE ) FROM pms_dept pd WHERE pd.dept_depth = 3 START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS dept_ZHIHANG,
	(SELECT max( DEPT_CODE ) FROM pms_dept pd WHERE pd.dept_depth = 4 START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS dept_WANGDIAN,

	
    (SELECT max( dept_name ) FROM pms_dept pd WHERE pd.dept_depth = 0  START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS "总行",
	(SELECT max( dept_name ) FROM pms_dept pd WHERE pd.dept_depth = 1  START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS "一级分行",
	(SELECT max( dept_name ) FROM pms_dept pd WHERE pd.dept_depth = 2  START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS "二级分行",
	(SELECT max( dept_name ) FROM pms_dept pd WHERE pd.dept_depth = 3 START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS "一级支行",
	(SELECT max( dept_name ) FROM pms_dept pd WHERE pd.dept_depth = 4 START WITH pd.dept_id = pd1.DEPT_ID connect BY prior pd.dept_parent_id = pd.dept_id ) AS "二级支行",
	 
	pd1.DEPT_ID            AS deptId,
	pd1.DEPT_CODE          AS DEPT_CODE,
	pd1.DEPT_NAME          AS "经办行"
	from PMS_DEPT pd1
	ORDER BY pd1.DEPT_LEVEL,pd1.DEPT_DEPTH

2.3 自顶向下

我们的需求是含有 该机构及所有上级机构关系 的展平表，如果采用自顶向下的方式，需要将获得数据进行一定的变形；

具体SQL如下：

SELECT 
REGEXP_SUBSTR(dp1, '[^-]+', 1,1)dept_zonghang,--正则表达式，截取第一次路径
REGEXP_SUBSTR(dp1, '[^-]+', 1,2)dept_shenghang,
REGEXP_SUBSTR(dp1, '[^-]+', 1,3)dept_shihang,
REGEXP_SUBSTR(dp1, '[^-]+', 1,4)dept_zhihang,
REGEXP_SUBSTR(dp1, '[^-]+', 1,5)dept_wangdian,
REGEXP_SUBSTR(dp2, '[^-]+', 1,1)总行,
REGEXP_SUBSTR(dp2, '[^-]+', 1,2)一级分行,
REGEXP_SUBSTR(dp2, '[^-]+', 1,3)二级分行,
REGEXP_SUBSTR(dp2, '[^-]+', 1,4)一级支行,
REGEXP_SUBSTR(dp2, '[^-]+', 1,5)经办网点,
A.DEPT_ID,
A.DEPT_CODE,
A.DEPT_NAME 经办行
from
(SELECT  DEPT_CODE,dept_id,DEPT_NAME,DEPT_DEPTH,
sys_connect_by_path(dept_code,'-') AS dp1,--获取dept_code递归路径
sys_connect_by_path(dept_name,'-') AS dp2--获取dept_name递归路径
FROM pms_dept pd   
START WITH pd.dept_id = 1 
connect BY prior pd.dept_id=pd.dept_parent_id) A
ORDER BY DEPT_DEPTH

sys_connect_by_path(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,',')。还有，这个函数使用之前必须先建立一个树，否则无用。将num值相等的。

得到的数据同上；

2.4通过DEPT_CODE的规律递归

DEPT_CODE自顶向下，每增加一级，字段长度增加3位；

我们可以通过该规律，编写对应的SQL：

SELECT
		substr( dept_code, 1, 3 ) AS dep_zonghang,
	CASE
     WHEN dept_depth > 0 THEN substr(dept_code, 1, 6) END AS dept_shenghang,
	CASE
			WHEN dept_depth > 1 THEN substr( dept_code, 1, 9) END AS dept_shihang,
	CASE
			WHEN dept_depth > 2 THEN substr( dept_code, 1, 12 ) END AS dept_zhihang,
	CASE	
			WHEN dept_depth > 3 THEN substr( dept_code, 1, 15 ) 	END AS dept_wangdian, 
	(select dept_name from pms_dept pd1 where pd1.dept_code = substr( pd.dept_code, 1, 3 ) ) AS "总行名称",
	(select dept_name from pms_dept pd1 where pd1.dept_code = substr( pd.dept_code, 1, 6 ) and dept_depth > 0 ) AS "一级分行",
	(select dept_name from pms_dept pd1 where pd1.dept_code = substr( pd.dept_code, 1, 9 ) and dept_depth > 1 ) AS "二级分行",
	(select dept_name from pms_dept pd1 where pd1.dept_code = substr( pd.dept_code, 1, 12 ) and dept_depth > 2 ) AS "一级支行",
	(select dept_name from pms_dept pd1 where pd1.dept_code = substr( pd.dept_code, 1, 15 ) and dept_depth > 3 ) AS "二级支行",
			dept_id,
			dept_code,
		dept_name AS "经办行"
	FROM
		pms_dept pd
		order by dept_depth

得到的数据同上；