MySQL 拼接表名递归寻找子节点和父节点

BEGIN
declare lev int;
-- 查询父级
   set lev=1;
   drop table if exists tmp1;    
   CREATE TABLE tmp1(ID VARCHAR(40),Wl_Code varchar(50),ParentCode varchar(40) ,levv INT);
     -- 用拼接的方式拼接表名和参数
   SET @sqlcmd = CONCAT('INSERT tmp1 SELECT ID,Wl_Code,ParentCode,1 FROM ',tablename ,' WHERE Wl_Code=',wlcode);
     -- 声明
      PREPARE stmt FROM @sqlcmd;  
   -- 执行SQL
   EXECUTE stmt;  
  -- DEALLOCATE PREPARE stmt; 
    -- SELECT ROW_COUNT();
  while  row_count()>0 
    do     set lev=lev+1;  
        -- 拼接SQL 和参数
        SET @sqlcmd = CONCAT(' INSERT tmp1 SELECT t.ID,t.Wl_Code,t.ParentCode,',lev,' from ',tablename,' t join tmp1 a on t.Wl_Code=a.ParentCode AND levv=',lev-1); 
     PREPARE stmt FROM @sqlcmd;  
   EXECUTE stmt;  
    end while ;
  -- SELECT DISTINCT ID,Wl_Code,ParentCode FROM tmp1;
-- 查询子级
SET lev=1;
-- 查询子级
   drop table if exists tmp2;    
   CREATE TABLE tmp2(ID VARCHAR(40),Wl_Code varchar(50),ParentCode varchar(40),levv INT);
SET @sqlcmd = CONCAT('INSERT tmp2 SELECT ID,Wl_Code,ParentCode,1 FROM ',tablename,' WHERE ParentCode=',wlcode); 
     PREPARE stmt FROM @sqlcmd;  
   EXECUTE stmt;  
  while  row_count()>0 
    do     set lev=lev+1;  
   SET @sqlcmd = CONCAT('INSERT tmp2 SELECT t.ID,t.Wl_Code,t.ParentCode,',lev,' from ',tablename,' t join tmp2 a on t.ParentCode=a.Wl_Code AND levv=',lev-1); 
     PREPARE stmt FROM @sqlcmd;  
   EXECUTE stmt;    
end while ;
-- SELECT DISTINCT ID,Wl_Code,ParentCode FROM tmp2;
SELECT DISTINCT ID,Wl_Code,ParentCode FROM tmp1 UNION ALL SELECT DISTINCT ID,Wl_Code,ParentCode FROM tmp2 order by ID ;
END

 

转载于:https://www.cnblogs.com/sanfor/p/7531684.html