MySQL根据id查询当前节点的层级

最新推荐文章于 2024-07-03 03:04:11 发布

差点是个神、

最新推荐文章于 2024-07-03 03:04:11 发布

阅读量515

点赞数

文章标签： SQL 递归查询层级结构自连接数据处理

原文链接：https://blog.csdn.net/HelloHangZhouwbm/article/details/85258844

版权

SELECT
	COUNT(*) 
FROM
	(
	SELECT
		tab2.id,
		tab2.NAME,
		tab2.p_id 
	FROM
		(
		SELECT
			@r AS _id,
			( SELECT @r := p_id FROM dc_location WHERE id = _id ) AS pid,
			@l := @l + 1 AS lvl 
		FROM
			( SELECT @r := #{id}, @l := 0 ) vars,
			dc_location h WHERE @r <> 0 ) tab1
		JOIN dc_location tab2 ON tab1._id = tab2.id 
	) tab1