mysql的递归查询

在水一方

发布于 2022-06-14 16:22:57

3.2K0

对于树结构的查询，在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