mysql - 如何从sql表中查找父子树

Question

我有一个表child，其中我有以下结构:

id  child_name  parent_id   parent_name
1   vikas        2           sunny
2   john         3           seema
3   ajay         4           delhi
4   josh         4           main

我正在尝试寻找父子，直到 parent_name 与 main 匹配，然后我会停止。如果可能的话，我需要单个查询 sql。

我试过了，但它只给我一个级别。

SELECT ChildUserType.child_name, 
       ChildUserType.parent_name,       
       ParentUserType.id, 
      ParentUserType.parent_id
FROM groups AS ChildUserType
  LEFT JOIN groups AS ParentUserType 
         ON ChildUserType.id = ParentUserType.parent_id 
where ChildUserType.id='1';

Answer 1

你可以试试这个:

CREATE PROCEDURE find_ancestry(IN id_init int)
 BEGIN
 DECLARE child_id int;
 DECLARE prev_id int;
 SET prev_id = id_init;
 SET child_id=0;
 SELECT parent_id into child_id 
 FROM groups WHERE id=id_init ;
 create TEMPORARY  table IF NOT EXISTS temp_table as (select * from groups where 1=0);
 truncate table temp_table;
 WHILE child_id <> 0 DO
   insert into temp_table select * from groups WHERE id=prev_id;
   SET prev_id = child_id;
   SET child_id=0;
   SELECT parent_id into child_id
   FROM groups WHERE id=prev_id;
 END WHILE;
 select * from temp_table;
 END //