无限级上级查询sql

SELECT
	a._id AS id,
	a.lvl,
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := pid
				FROM
					users
				WHERE
					uid = _id
			) AS pid ,@l := @l + 1 AS lvl
		FROM
			(SELECT @r :=1111, @l := 0) vars,
			users h
		WHERE
			@r <> 0
	) a
JOIN users b ON a._id = b.id
AND b.pid >= 0
AND a._id <> 1111
ORDER BY
	a.lvl ASC
LIMIT 10

mysql8.0 下where @r <> 0无效果 使用having _id <> 0

 

SELECT
	a._id AS id,
	a.lvl,
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := pid
				FROM
					users
				WHERE
					uid = _id
			) AS pid ,@l := @l + 1 AS lvl
		FROM
			(SELECT @r :=1111, @l := 0) vars,
			users h
		HAVING
			_id <> 0
	) a
JOIN users b ON a._id = b.id
AND b.pid >= 0
AND a._id <> 1111
ORDER BY
	a.lvl ASC
LIMIT 10