记一次Sql优化过程——表自连接查询所有的子孙节点

delimiter //

drop PROCEDURE IF EXISTS  showChildLst//

CREATE PROCEDURE showChildLst (IN rootid INT)
BEGIN
 DECLARE Level int ;
 drop TABLE IF EXISTS tmpLst;
 CREATE TABLE tmpLst (
  id int,
  nLevel int,
  sCort varchar(8000)
 );
 
 Set Level=0 ;
 INSERT into tmpLst SELECT id,Level,ID FROM crm_presona_dk WHERE presona_pid=rootid;
 WHILE ROW_COUNT()>0 DO
  SET Level=Level+1 ;
  INSERT into tmpLst 
   SELECT A.id,Level,concat(B.sCort,'-',A.id) FROM crm_presona_dk A,tmpLst B 
    WHERE  A.presona_pid=B.id AND B.nLevel=Level-1  ;
 END WHILE;
  
END;
//

delimiter ;

call showChildLst(2);
select * from tmpLst;