查询所有上级和所有下级

-- 查询自己和所有上级
SELECT
	T0.*,
	T1.dept_name,
	T1.parent_id
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := parent_id FROM sys_dept WHERE dept_id = _id ) AS parent_id1,
		@l := @l + 1 AS lvl 
	FROM
		( SELECT @r := '103', @l := 0 ) vars,
		sys_dept h 
	WHERE
		@r <> 0 
	) T0
	JOIN sys_dept T1 ON T0._id = T1.dept_id 
ORDER BY
	dept_id;
	




-- 查询自己和所有下级
SELECT
	DATA.*
FROM
	(
	SELECT
		@ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( dept_id ) FROM sys_dept WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
		@l := @l + 1 AS lvl 
	FROM
		sys_dept,
		( SELECT @ids := '102', @l := 0 ) b 
		
	WHERE
		@ids IS NOT NULL 
	) id,
	sys_dept DATA 
WHERE
	FIND_IN_SET( DATA.dept_id, id._ids ) 
ORDER BY
	lvl,
	dept_id;