MySQL之非存储过程查询所有父级节点

• 例如 祖级节点为：企业/TI技术部/后端
• 假设后端节点的id 为1416957672636129281
• 需求：需要查询出后端所有的父级级节点，即企业/TI技术部

SELECT
	t2.* 
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := parent_id FROM sys_dept WHERE id = _id ) AS parent_id,
		@s := @s + 1 AS sort 
	FROM
		( SELECT @r := 1416957672636129281, @s := 0 ) temp,
		sys_dept 
	WHERE
		@r > 0 
	) t1
	JOIN sys_dept t2 ON t1._id = t2.id 
ORDER BY
	t1.sort DESC

• 此SQL会查询出包括本身数据，即1416957672636129281的数据
• 最终查询结果为：企业、TI技术部、后端