mysql 数据合并——供大家参考借鉴 [ 技术分享 ]

[attach]208[/attach] 图中有三个表格，本贴主要讲怎样将图中表1的数据合并成表2的形式或表3的形式。注：表3中数据之间的*号为分隔符并非乘号。 本贴是通过mysql的存储过程来实现。以下为存储过程代码，代码中有用到用户自定义函数有

（split_result，func_get_split_string，func_get_split_string_total），BEGIN
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET SQL_SAFE_UPDATES=0;
DELETE FROM report_passrate WHERE REPORT_TYPE = report_type;  -- The first run ,please note this line
COMMIT;-- The first run ,please note this line
INSERT INTO report_passrate
SELECT D.XXWO_NBR,D.XXCON_NBR,E.XXCON_PART,E.XXCON_DEP,E.XXCON_LINE,E.XXCON_OP,SUM(E.XXCON_QTY_CHK) CHK,SUM(E.XXCON_QTY_REJ) REJ,
TRUNCATE(1-SUM(E.XXCON_QTY_REJ)/SUM(E.XXCON_QTY_CHK),2) AVG, report_type REPORT_TYPE
FROM
    (SELECT
    X.XXWO_NBR,X.XXWO_DTE01,X.XXCON_NBR,X.XXCON_DEP,X.XXCON_DATE,X.XXCON_LINE,X.XXCON_PART,
    truncate(split_result(GROUP_CONCAT(X.AVG SEPARATOR "*"),'*'),2) AVG
    FROM
        (SELECT 
                XXWO_NBR,
                XXWO_DTE01,
                XXCON_NBR,
                XXCON_DEP,
                XXCON_DATE,
                XXCON_LINE,
                XXCON_OP,
                XXCON_PART,
                SUM(XXCON_QTY_CHK) CHK,
                SUM(XXCON_QTY_REJ) REJ,
                truncate(1-SUM(XXCON_QTY_REJ)/SUM(XXCON_QTY_CHK),2) AVG
         FROM
            (SELECT XXWO_NBR,XXWO_DTE01
             FROM xxwo_mstr
             WHERE XXWO_DTE01 BETWEEN start_date AND end_date
             AND XXWO_NBR LIKE wo_no 
             AND XXWO_PART LIKE part ) A, xxcon_det B
         WHERE  A.XXWO_NBR = B.XXCON_CHECKER
         AND B.XXCON_DEP LIKE dep
         GROUP BY XXCON_PART,XXCON_OP
         ORDER BY XXCON_PART) X
    GROUP BY X.XXWO_NBR
    ORDER BY truncate(split_result(GROUP_CONCAT(X.AVG SEPARATOR "*"),'*'),2) ASC,X.XXCON_PART
    limit 0,10) D,xxcon_det E
WHERE D.XXWO_NBR = E.XXCON_CHECKER
AND E.XXCON_OP LIKE op
GROUP BY E.XXCON_PART,E.XXCON_OP;


  -- start creat table struct;
  drop table IF EXISTS report2; -- The first run ,please note this line
  drop table IF EXISTS report3;-- The first run ,please note this line
  set @rtype = report_type;
  select @count:=count(*) from (select count(*) from report_passrate where REPORT_TYPE=@rtype group by XXCON_OP) a;
  set @cc = 0;
  create table IF NOT EXISTS report2(XXWO_NBR VARCHAR(30) NOT NULL DEFAULT '',XXCON_PART VARCHAR(100) not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  while @cc < @count do
    prepare stmt1 from 'select @str:=XXCON_OP from report_passrate where REPORT_TYPE=@rtype group by XXCON_OP limit ?,1';
    set @num = @cc;
    EXECUTE stmt1 USING @num;
    DEALLOCATE PREPARE stmt1;
    set @stmt2 = concat('alter table report2 add `',@str,'` varchar(255) not null default \'\'');
    prepare stmt2 from @stmt2;
    execute stmt2;
    deallocate prepare stmt2;
    set @cc = @cc+1;
  end while;
  -- end creat table struct;
  -- start write data to table
  select @count2:=count(*) from(select count(*) from report_passrate where REPORT_TYPE=@rtype group by XXWO_NBR) a;
  set @cc2 = 0;
  while @cc2 <= @count2 do
    prepare stmt3 from 'select @wo:=XXWO_NBR,@part:=XXCON_PART from report_passrate where REPORT_TYPE=@rtype group by XXWO_NBR limit ?,1';
    set @num2 = @cc2;
    execute stmt3 using @num2;
    insert into report2 set XXWO_NBR=@wo, xxcon_part=@part;
    deallocate prepare stmt3;
    set @cc2 = @cc2+1;
  end while;

  drop table IF EXISTS report3;-- The first run ,please note this line
  CREATE TABLE IF NOT EXISTS report3 SELECT * FROM report2 where 0;
  set @ct = 0;
  while @ct < 10 do
    prepare st6 from 'select @wo:=XXWO_NBR from report2 limit ?,1;';
    execute st6 using @ct;
    set @str = '';
    set @cc4 = 2;
    while @cc4 < (@count+2) do
      set @stmt5 = concat('select @datas:=b.datas from information_schema.columns a left join (SELECT @wo:=XXWO_NBR,@part:=XXCON_PART,XXCON_OP,concat_ws(\'*\',CHK,REJ,AVG) datas FROM report_passrate c where c.REPORT_TYPE=@rtype and c.XXWO_NBR = \'',@wo,'\') b on (a.column_name = b.xxcon_op) where a.table_name=\'report2\' and a.table_schema = \'cesmis\' limit ',@cc4,',1;');
      prepare st5 from @stmt5;
      execute st5;
      set @str = concat(@str,'"',IFNULL(@datas,''),'",');
      set @cc4 = @cc4 + 1;
    end while;
    set @str = left(@str,length(@str)-1);
    set @str = concat('select "',@wo,'","',@part,'",',@str);
    select @str;
    set @stmt5 = concat('insert into report3 ',@str,';');
    prepare st5 from @stmt5;
    execute st5;
    deallocate prepare st5;
    set @stmt5 = '';
    set @stmt6 = '';
    set @ct = @ct + 1;
  end while;
  drop table IF EXISTS report2;
  -- end write data to table
  -- Return a success value
  -- End reutrn
COMMIT;
SET AUTOCOMMIT = 1;

END[/code]用户自定义函数split_result代码：[code]BEGIN
  -- Get the separated string.
  declare cnt int default 0;
  declare i int default 0;
  declare rs_return float default 1;
  set cnt = func_get_split_string_total(f_string,f_delimiter);

  while i < cnt
  do
    set i = i + 1;
    set rs_return =func_get_split_string(f_string,f_delimiter,i)*rs_return;
    -- insert into tmp_print(num) values (func_get_split_string(f_string,f_delimiter,i));
  end while;
  -- return rs_return;
  return rs_return;
END

用户自定义函数func_get_split_string代码：

BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END

用户自定义函数func_get_split_string_total代码：

BEGIN
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END