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

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

 

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

标签：insert   sql   style   ntc   col   arch   tin   class   eal