目 录CONTENT

文章目录

mysql的递归查询

在水一方
2022-04-03 / 0 评论 / 0 点赞 / 472 阅读 / 683 字 / 正在检测是否收录...

对于树结构的查询,在oracle数据库中有现成的函数直接调用,但是在mysql中这部分没有现成的函数可以直接调用,对于树形结构的递归遍历在实际业务中也是非常常见的。本小节做一个记录

向下递归查询

SELECT
	ID.LEVEL,
	DATA.* 
FROM
	(
	SELECT
		@ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父级 id字段, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL 
	FROM
		表名,
		( SELECT @ids := '条件id', @l := 0 ) b 
	WHERE
		@ids IS NOT NULL 
	) ID,
	表名 DATA 
WHERE
	FIND_IN_SET( DATA.id, ID._ids ) 
ORDER BY
	LEVEL,
	id



向上递归


SELECT
	GROUP_CONCAT( s.name SEPARATOR "," ) 
FROM
	(
	SELECT
		T2.id,
		T2.NAME 
	FROM
		(
		SELECT
			@r AS _id,
			( SELECT @r := PK_FATHERORG FROM sys_mdm_org WHERE PK_ORG = _id ) AS 2v2,
			@l := @l + 1 AS lvl 
		FROM
			( SELECT @r := '0001A41000000010JDYT' ) vars,
			sys_mdm_org h 
		WHERE
			@r <> 0 
		) T1
		JOIN sys_mdm_org T2 ON T1._id = T2.pk_org 

	) s 



image.png

@用来标识用户变量

image.png
https://blog.csdn.net/qq_37493556/article/details/108296542

0

评论区