各位大虾,还是昨天的那个问题,今天传上图片。
图片中第一个表的记录是不固定的,也就是xxcon_dep的记录不是固定的几个,可能有十几个、二十几个。
大家看一下怎么能做出来。
+-------------------------------------------------------------------------------------------------+
+XXCON_PART --------|------ XXCON_OP ---|--- XXCON_QTY_CHK ---|--- XXCON_QTY_REJ ---|--- QTY_AVG--|
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | HA | 18 | 4 | 0.77 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | W3 | 9 | 0 | 1.00 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | FA | 19 | 1 | 0.94 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | FB | 22 | 4 | 0.81 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | FA | 184 | 33 | 0.82 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | W1 | 197820 | 14 | 0.99 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | HA | 76 | 7 | 0.90 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | W2 | 2700 | 18 | 0.99 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | W3 | 6507 | 16 | 0.99 |
+-------------------------------------------------------------------------------------------------+
希望合并成以下形式
+------------------------------------------------------------------------------------------------------------+
+XXCON_PART | HA | W3 | FA | FB | W1 | W2 |
+-----------------+---------------+----------------+--------------+--------------+--------------+------------+
D1301-028-295-5531| 18 | 4 | 0.77 | 9 | 0 | 1.00 | 19 | 1 | 0.94| 22 | 4 | 0.81| | |
+-----------------+---------------+----------------+--------------+--------------+--------------+------------+
1011404 | 76 | 7 | 0.90 | 6507| 16| 0.99 | |184 | 33| 0.82|197820|14|0.99|2700|18|0.99|
+------------------------------------------------------------------------------------------------------------+
图片中第一个表的记录是不固定的,也就是xxcon_dep的记录不是固定的几个,可能有十几个、二十几个。
大家看一下怎么能做出来。
+-------------------------------------------------------------------------------------------------+
+XXCON_PART --------|------ XXCON_OP ---|--- XXCON_QTY_CHK ---|--- XXCON_QTY_REJ ---|--- QTY_AVG--|
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | HA | 18 | 4 | 0.77 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | W3 | 9 | 0 | 1.00 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | FA | 19 | 1 | 0.94 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+D1301-028-295-5531 | FB | 22 | 4 | 0.81 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | FA | 184 | 33 | 0.82 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | W1 | 197820 | 14 | 0.99 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | HA | 76 | 7 | 0.90 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | W2 | 2700 | 18 | 0.99 |
+-------------------+-------------------+---------------------+---------------------+-------------+
+1011404 | W3 | 6507 | 16 | 0.99 |
+-------------------------------------------------------------------------------------------------+
希望合并成以下形式
+------------------------------------------------------------------------------------------------------------+
+XXCON_PART | HA | W3 | FA | FB | W1 | W2 |
+-----------------+---------------+----------------+--------------+--------------+--------------+------------+
D1301-028-295-5531| 18 | 4 | 0.77 | 9 | 0 | 1.00 | 19 | 1 | 0.94| 22 | 4 | 0.81| | |
+-----------------+---------------+----------------+--------------+--------------+--------------+------------+
1011404 | 76 | 7 | 0.90 | 6507| 16| 0.99 | |184 | 33| 0.82|197820|14|0.99|2700|18|0.99|
+------------------------------------------------------------------------------------------------------------+
+------------------------------------------------------------------------------------------------------------+
+XXCON_PART | HA | W3 | FA | FB | W1 | W2 |
+-----------------+---------------+----------------+--------------+--------------+--------------+------------+
D1301-028-295-5531| 18 | 4 | 0.77 | 9 | 0 | 1.00 | 19 | 1 | 0.94| 22 | 4 | 0.81| | |
+-----------------+---------------+----------------+--------------+--------------+--------------+------------+
1011404 | 76 | 7 | 0.90 | 6507| 16| 0.99 | |184 | 33| 0.82|197820|14|0.99|2700|18|0.99|
+------------------------------------------------------------------------------------------------------------+只有7个字段?
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
以下是我的存储过程,第一次写这么长的过程,写得不好,不过思路还是比较明确的。
再次感谢你们帮助我!!DELIMITER $$DROP PROCEDURE IF EXISTS `proc_passrate` $$
CREATE DEFINER=`cessoftuser`@`%` PROCEDURE `proc_passrate`(IN rtype varchar(1))
BEGIN
-- start creat table struct;
drop table IF EXISTS report2;
drop table IF EXISTS report3;
set @rtype = rtype;
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;
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
END $$DELIMITER ;
'D1001028', '7990019-03', '', '', '65*28*0.56', '', '', '', '', '', '91450*6*0.99', '8864*0*1.00'
'D1001031', '7990192', '', '180*48*0.73', '30*8*0.73', '', '', '', '', '', '13984*42*0.99', ''
'D1001070', 'D1501-012-280-5668', '', '28*0*1.00', '70*10*0.85', '59*0*1.00', '', '7*1*0.85', '28*0*1.00', '100*0*1.00', '82*3*0.96', ''
'D1001073', '03-0244-00', '', '', '', '', '', '30*3*0.90', '', '45*3*0.93', '', '103*38*0.63'
'D1001075', '03-0245-00', '', '', '', '', '', '34*0*1.00', '', '16*1*0.93', '14*0*1.00', '32*15*0.53'
'D1001083', '7200-000189', '', '', '174*52*0.70', '', '', '994*23*0.97', '', '', '', ''
'D1001148', '7990038', '69*0*1.00', '', '60*1*0.98', '', '', '3*3*0.00', '43600*6*0.99', '', '6760*0*1.00', '7488*97*0.98'
'D1002121', '7990262', '', '', '', '', '', '', '', '', '', '105*30*0.71'
'D1002130', '7990070-02', '', '552*3*0.99', '1560*21*0.98', '100*2*0.98', '8400*1*0.99', '', '18*8*0.55', '60*2*0.96', '1934*14*0.99', '151500*59*0.99'
'DGA01005', 'R2250-A211', '', '', '', '', '', '', '', '', '', '10*5*0.50'