MySQL 拼接表名遞迴尋找子節點和父節點

標籤：insert   sql   style   ntc   col   arch   tin   class   eal   

BEGINdeclare 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 拼接表名遞迴尋找子節點和父節點