查询 id = 6 的所有父级

SELECT ID.level, DATA.* FROM( SELECT @id as _id, ( SELECT @id := parent_id FROM table1 WHERE id = @id ) as _pid, @l := @l+1 as level FROM table1, (SELECT @id := 6, @l := 0 ) b WHERE @id > 0 ) ID, table1 DATA WHERE ID._id = DATA.id ORDER BY level;

根据这个父级查询方法，很容易可以写出查所有子级的，下面的查询?id=3?的所有子级

SELECT ID.level, DATA.* FROM( SELECT @ids as _ids, ( SELECT @ids := GROUP_CONCAT(id) FROM table1 WHERE FIND_IN_SET(parent_id, @ids) ) as cids, @l := @l+1 as level FROM table1, (SELECT @ids :=3, @l := 0 ) b WHERE @ids IS NOT NULL ) id, table1 DATA WHERE FIND_IN_SET(DATA.id, ID._ids) ORDER BY level, id