mysql 树结构递归查询

最新推荐文章于 2024-09-05 09:02:28 发布

原创最新推荐文章于 2024-09-05 09:02:28 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种使用SQL进行向下和向上递归查询的方法，通过变量和子查询实现对数据库表中层级关系的遍历，提供了简洁的SQL语句示例。

--向下递归查询
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语句简练考虑，如有问题请大家多多请教