mysql動態sql 整理多個欄位

標籤：exe   trim   tmp   for   one   技術分享   open   code   動態   

原始表：

整理後的表：

 

方案一（動態sql）：

BEGIN    #Routine body goes here...    DECLARE v1 int(3);    DECLARE v2 int(3);    #DECLARE v3 VARCHAR(15);        #DECLARE vcompany VARCHAR(30);    #DECLARE vname VARCHAR(30);    DECLARE vcol VARCHAR(30);    DECLARE tmp_sql VARCHAR(3000);    DECLARE vid int(3);    set v1 = 1;    WHILE v1 <= 11993 DO        set v2=2;        #if v2 <=27 THEN         WHILE v2 <=27  DO            set @v3=CONCAT(‘l‘,v2);            set @vcompany=‘‘;            set @vname=‘‘;            #set @vid=‘‘;                        SET @l_sql=CONCAT_ws(‘ ‘,                ‘select company_name,‘,@v3,                ‘into @vcompany,@vname‘,                ‘from for_xun_cha where id =‘,v1);            SET @sql=@l_sql;            prepare stmt from @sql;            execute stmt;                #select company_name,l2 into vcompany,vname from for_xun_cha where id=v2;            if LENGTH(trim(@vname)) > 0 THEN                INSERT into tmp(company,name) VALUES(@vcompany,@vname);                #INSERT into tmp(company,name) VALUES(vcompany,tmp_sql);            end if;             set v2 = v2+1;        #end if;        end WHILE;        set v1 = v1 + 1;    END WHILE;    END

方案二（先合并各列，再用遊標處理）：

BEGINDECLARE Done INT DEFAULT 0;DECLARE n int ; -- 最大列 27DECLARE companyName VARCHAR(20) ;-- 公司名稱DECLARE personNames VARCHAR(1000);-- 員工名稱DECLARE personName VARCHAR(10);DECLARE _cur CURSOR FOR select company_name ,CONCAT_WS(",",l2,l3,l4,l5,l6,l7,l8,l9,l10,l11,l12,l13,l14,l15,l16,l17,l18,l19,l20,l21,l22,l23,l24,l25,l26,l27) from for_xun_cha;OPEN _cur;    FETCH _cur INTO companyName,personNames;    REPEAT    IF NOT Done THEN         test:    WHILE(n<=27) DO            set personName=SUBSTRING_INDEX(SUBSTRING_INDEX(personNames,‘,‘,n),‘,‘,-1);                if  ISNULL(personName) || LENGTH(trim(personName))<1 THEN                        LEAVE test;                ELSE                    INSERT INTO temp1 VALUES (companyName,personName);                END IF;            set n=n+1;            end while;    end IF;set n = 1;    FETCH NEXT FROM _cur INTO companyName,personNames;    UNTIL Done END REPEAT;-- set personName="";-- set personNames="";CLOSE _cur;END

 

mysql動態sql 整理多個欄位