mysql 树形数据根据某个元素查询最顶级元素、根据顶级父类(递归)查询其子节点下所有元素

最新推荐文章于 2024-05-12 23:27:37 发布

机智皮皮涛

最新推荐文章于 2024-05-12 23:27:37 发布

阅读量921

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_41285292/article/details/130250822

版权

需要字段parent_id关联树形结构

根据元素id 查询最顶级元素

SELECT
	id 
FROM
	(
	SELECT
		t1.id,
	IF
		( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild 
	FROM
		( SELECT id, parent_id FROM c_dict t WHERE t.deleted = 0 ORDER BY parent_id, id ) t1,
		( SELECT @pids := '21' ) t2 
	) t3 
WHERE
	ischild != '0'

根据顶级父类id (递归)查询其子节点下所有元素id

SELECT t2.id
	FROM 
	(
	SELECT 
	@r AS _id, 
	(SELECT @r := parent_id FROM c_dict WHERE id = _id) AS parent_id, 
	@l := @l + 1 AS lvl 
	FROM 
	(SELECT @r := '2c9180837bc8f515017bc9b9cb1403d0', @l := 0) vars, c_dict AS h 
	
	) t1 
	JOIN c_dict t2 
	ON t1._id = t2.Id WHERE t2.parent_id is null