202111-17更文-pgsql中 WITH RECURSIVE递归查询的使用

这是我参与11月更文挑战的第17天，活动详情查看：2021最后一次更文挑战

pgsql中 WITH RECURSIVE递归查询的使用

前文

本文主要来源是工作中使用pgsql时，遇到了需要进行树关系查询的场景。经过查找资料，发现使用WITH RECURSIVE进行递归查询是一种比较好的解决方案。因此在此进行一些内容记录，主要为个人理解，不足之处还请见谅。

问题场景描述

简单来说，目前有一系列资产表的数据，数据之间利用parent_id进行关联，组合成一棵资产树结构。现在想要将所有关联树中的资产从资产表中查询出来。在此场景下，利用WITH RECURSIVE进行递归是一种比较好的处理方式。

解决方案

首先来看一下使用WITH RECURSIVE的查询语句，以及利用该语句查询的结果截图。

WITH RECURSIVE T AS (
	SELECT ID
		,
		identifier,
		asset_type,
		asset_name,
		( '' || asset_name ) AS PATHNAME,
		description 
	FROM
		am_tree_asset 
	WHERE
		parent_asset_id IS NULL 
		AND del_flag = 0 UNION ALL
	SELECT
		D.ID,
		D.identifier,
		D.asset_type,
		D.asset_name,
		( T.PATHNAME || '/' || D.asset_name ) AS PATHNAME,
		D.description 
	FROM
		am_tree_asset D
		JOIN T ON D.parent_asset_id = T.ID 
		AND D.del_flag = 0 
	) 
SELECT
	T.ID,
	T.identifier,
	T.asset_name,
	T.pathname,
	T.description 
FROM
T

如上所示，可以轻易的看出资产01、资产02...资产05之间存在层级间的父子结构关系。经过递归查询操作，可以看到我们将该树下的所有相关资产都查询出来。下面对查询的sql语句进行一下探索。首先我们采用了WITH RECURSIVE进行递归查询，查询的第一条数据指定为parent_id为null的数据。同时利用union all进行数据拼接，拼接下利用id与parent_id的关联关系进行处理。很明显，递归操作的意思就是在执行的过程中，不停的将之前的查询结果赋值给递归查询中使用的存储集T。然后再通过union all进行数据拼接，将符合条件的下一级数据通过条件进行关联。通过这种递归方式，使得查询的数据结果不停的膨胀，直至查出所有满足条件的数据为止。然后通过对于结果集t的查询，即可得到所有的目标数据。

后记

• 千古兴亡多少事？悠悠。不尽长江滚滚流。