MYSQL 查询所有上级and查询所有下级的sql

前言:

      最近做的项目很多都有邀请机制,如果在代码中使用递归查询,感觉不太希望这样做,既浪费资源又麻烦,所以希望直接在sql中,直接一次查询出来,然后在根据业务做一些其他的操作........

1.查询所有上级并查询出层级

SELECT
	T0.*
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					t_user
				WHERE
					id = _id
			) AS parent_id1,
			@l := @l + 1 AS lvl
		FROM
			(SELECT @r := 2, @l := 0) vars, -- 这里的2是用户id
			t_user h
		WHERE
			@r <> 0
	) T0
JOIN t_user T1 ON T0._id = T1.id
ORDER BY id;

 

2.查询所有下级并查询出层级

SELECT
	*
FROM
	(
		SELECT
			@ids AS _ids,
			(
				SELECT
					@ids := GROUP_CONCAT(id)
				FROM
					t_user
				WHERE
					FIND_IN_SET(parent_id, @ids)
			) AS cids,
			@l := @l + 1 AS lvl
		FROM
			t_user,
			(SELECT @ids := 1, @l := 0) b -- 这里的1是用户id
		WHERE
			@ids IS NOT NULL
	) id,
	t_user DATA
WHERE
	FIND_IN_SET(DATA .id, id._ids)
ORDER BY
	lvl,
	id