mysql5.7根据节点查找所有的父节点包括自己

一、表结构

二、sql

比如一个结构为【数智知识库-数智项目-IT建设】根据IT建设的主键ID查询所有父级
1747814477764300801 是【IT建设】的主键ID

SELECT ca.id, ca.name ,ca.introduce,ca.level FROM catalogue ca
JOIN (
  SELECT @pv := (SELECT pid FROM catalogue WHERE id = 1747814477764300801) AS nodeid
  UNION ALL
  SELECT @pv := (SELECT pid FROM catalogue WHERE id = @pv ) FROM catalogue WHERE @pv IS NOT NULL
	union all 
	select id from catalogue  WHERE id = 1747814477764300801
) a ON ca.id = a.nodeid
order by ca.level asc;

三、查询结果