postgresql使用with recursive完成迭代查询

最新推荐文章于 2024-07-23 17:27:33 发布

小城南

最新推荐文章于 2024-07-23 17:27:33 发布

阅读量4.3k

点赞数 1

分类专栏： mybatis 数据库文章标签：递归 with recursive as 子查询

本文链接：https://blog.csdn.net/cainiaobulan/article/details/82220091

版权

数据库同时被 2 个专栏收录

32 篇文章 3 订阅

订阅专栏

mybatis

15 篇文章 0 订阅

订阅专栏

需求：

查询某条记录的所有父亲节点，或者所有孩子节点。

表结构如下：（id是当前节点，super_cust_id是父节点，-1表示根节点）

查询节点以及所有子节点sql语句：

<select id="queryNodesByParentId" resultMap="resultMap" parameterType="com.fh.iasp.app.cm.v2.custrel.pojo.BasCmCustomerRel">
		WITH RECURSIVE cust AS (
		SELECT
		r.*
		FROM
		bas_cm_customer_rel r
		WHERE
		r.status = '1'
		<if test="id != null">
			and r.id = #{id,jdbcType=BIGINT}
			UNION ALL
			SELECT
			rel.*
			FROM bas_cm_customer_rel rel
			JOIN cust AS B ON rel.super_cust_id = B. ID
		</if>
		) SELECT
		cust.*
		FROM cust;
	</select>

查询节点以及所有父节点sql语句：

<select id="queryNodesByParentId" resultMap="resultMap" parameterType="com.fh.iasp.app.cm.v2.custrel.pojo.BasCmCustomerRel">
		WITH RECURSIVE cust AS (
		SELECT
		r.*
		FROM
		bas_cm_customer_rel r
		WHERE
		r.status = '1'
		<if test="id != null">
			and r.id = #{id,jdbcType=BIGINT}
			UNION ALL
			SELECT
			rel.*
			FROM bas_cm_customer_rel rel
			JOIN cust AS B ON rel.ID = B.super_cust_id 
		</if>
		) SELECT
		cust.*
		FROM cust;
	</select>