如何让MySQL中单句实现无限层次父子关系查询

表结构和数据

CREATE TABLE table1(id int, name varchar(10), parent_id int)

INSERT table1 VALUES

(1, ‘Home’, 0),

(2, ‘About’, 1),

(3, ‘Contact’, 1),

(4, ‘Legal’, 2),

(5, ‘Privacy’, 4),

(6, ‘Products’, 1),

(7, ‘Support’, 2)

查询 id = 5 的所有父级

SELECT ID.level, DATA.* FROM(

SELECT

@id as _id,

( SELECT @id := parent_id

FROM table1

WHERE id = @id

) as _pid,

select t1.name,t2.name,t3.name,t4.name

from tb as t1

left join tb as t2 on t2.pid=t1.id and t2.pid=1

left join tb as t3 on t3.pid=t2.id and t3.pid=2

left join tb as t4 on t4.pid=t3.id and t4.pid=3

where pid=0

---