mysql 树结构递归查询

最新推荐文章于 2024-09-19 17:37:23 发布

qsdnmd

最新推荐文章于 2024-09-19 17:37:23 发布

阅读量843

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qsdnmd/article/details/87875781

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

--向下递归查询
SELECT
	T2.*, T1.lvl
FROM
	(
		SELECT
			@r AS _parent_id,
			(
				SELECT
					@r := group_concat(id)
				FROM
					tab_test
				WHERE
					find_in_set(parent_id, _parent_id) > 0
			) AS parent_id,
			@l := @l + 1 AS lvl
		FROM
			(SELECT @r := 1, @l := 0) vars,
			tab_test h
		WHERE
			@r is not NULL
	) T1
JOIN tab_test T2 ON find_in_set(T2.id, T1.parent_id) > 0;
--向上递归查询
SELECT
	T2.*, T1.lvl
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					tab_test
				WHERE
					id = _id
			) AS parent_id,
			@l := @l + 1 AS lvl
		FROM
			(SELECT @r := 1, @l := 0) vars,
			tab_test h
	) T1
JOIN tab_test T2 ON T1._id = T2.id;

sql语句未仔细斟酌性能等其它问题，只单以sql语句简练考虑，如有问题请大家多多请教