mysql家譜表查詢某人所有后代

CREATE?TABLE?`people`?(  ????`id`?INT(11)?NOT?NULL,  ????`name`?VARCHAR(50)?NULL?default?NULL,  ????`pid`?INT(11)?NOT?NULL?DEFAULT?'0',  ????PRIMARY?KEY?(`id`)  );  ?  CREATE?DEFINER=`root`@`%`?PROCEDURE?`getChildren`(IN?`parentId`?INT)  ????LANGUAGE?SQL  ????NOT?DETERMINISTIC  ????CONTAINS?SQL  ????SQL?SECURITY?DEFINER  ????COMMENT?'獲取某人所有后代'  BEGIN  ????#存放結(jié)果的臨時(shí)表  ????DROP?TABLE?IF?EXISTS?children;  ????CREATE?TEMPORARY?TABLE?children?SELECT?0?pLevel,p.*?FROM?`people`?p?WHERE?id=parentId;  ????#存放中間結(jié)果的臨時(shí)表  ????DROP?TABLE?IF?EXISTS?tem;  ????CREATE?TEMPORARY?TABLE?tem?SELECT?id?FROM?`people`?limit?0;  ?????  ????#逐級(jí)填充后代  ????SET?@pLevel=1;  ????REPEAT  ????????#清空上次數(shù)據(jù)  ????????TRUNCATE?TABLE?tem;  ????????#將當(dāng)前l(fā)evel的后代id放入臨時(shí)表  ????????INSERT?INTO?tem?SELECT?p.id?FROM?`people`?P,?children?c?  ????????????WHERE?p.pid=c.id?AND????c.pLevel=(@pLevel-1);  ????????#將當(dāng)前l(fā)evel的后代數(shù)據(jù)塞入結(jié)果臨時(shí)表  ????????INSERT?INTO?children?SELECT?@pLevel?pLevel,p.*?FROM?`people`?p,?tem?t?  ????????WHERE?p.id=t.id;  ????????SET?@pLevel=@pLevel+1;  ????UNTIL?NOT?EXISTS?(SELECT?*?FROM?tem)?OR?@pLevel?>?10  ????END?REPEAT;  ?????  ????#調(diào)整表結(jié)構(gòu)，刪除臨時(shí)列和不需要的數(shù)據(jù)  ????ALTER?TABLE?children?DROP?COLUMN?pLevel;  ????DELETE?FROM?children?WHERE?id=parentId;  ?????  ????#返回結(jié)果  ????SELECT?*?from?children;  END