mysql查询父节点_MySql子父节点查询

一、在navicat里创建一个类似这样的关系的表格

二、递归查询子节点：

> SELECT

FROM

(

SELECT

@id idlist,

( SELECT @id := GROUP_CONCAT( id SEPARATOR ',' ) FROM test1 WHERE FIND_IN_SET( parent_id, @id ) ) sub

FROM

test1,

( SELECT @id := 3 ) vars

WHERE

@id IS NOT NULL

) tl,

test1 t

WHERE

FIND_IN_SET( t.id, tl.idlist );

查询结果：

三、递归查询父节点：

SELECT

FROM

(

SELECT

@id idlist,

( SELECT @id := GROUP_CONCAT( parent_id SEPARATOR ',' ) FROM test1 WHERE FIND_IN_SET( id, @id ) ) sub

FROM

test1,

( SELECT @id := 3 ) vars

WHERE

@id IS NOT NULL

) tl,

test1 t

WHERE

FIND_IN_SET( t.id, tl.idlist );

查询结果：