mysql 多张表汇总成一张表问题

[ydl1315]

想要将多张表表结构不完全一样（这里提问就简单点就用一样的）的表汇总成一张表，网上查了很多关于表连接的join ，union等使用，但是没有找到合适我的需求的，求大家帮忙看看。3张表分别是3个项目的人员测试得分，每个项目可以多次测试，如图表test1，test2，test3。需要将3个项目测试表汇总成一张表，每个人员每项目测试值都需要单独一行列出来，如图test，null单元格如果做不到也接受填充历史值。mysql建库和建表语句如下：
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;

CREATE TABLE `test` (
`name` varchar(10),
`value1` double,
`value2` double,
`value3` double
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test1` (
`name` varchar(10) NOT NULL,
`value1` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test2` (
`name` varchar(10) NOT NULL,
`value2` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test3` (
`name` varchar(10) NOT NULL,
`value3` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test`.`test1`(`name`,`value1`)VALUES('小明',76);
INSERT INTO `test`.`test1`(`name`,`value1`)VALUES('小李',60);
INSERT INTO `test`.`test1`(`name`,`value1`)VALUES('小李',88);

INSERT INTO `test`.`test2`(`name`,`value2`)VALUES('小明',55);
INSERT INTO `test`.`test2`(`name`,`value2`)VALUES('小李',95);
INSERT INTO `test`.`test2`(`name`,`value2`)VALUES('小明',69);
INSERT INTO `test`.`test2`(`name`,`value2`)VALUES('小明',85);
INSERT INTO `test`.`test2`(`name`,`value2`)VALUES('小李',93);
INSERT INTO `test`.`test2`(`name`,`value2`)VALUES('小李',88);

INSERT INTO `test`.`test3`(`name`,`value3`)VALUES('小李',91);
INSERT INTO `test`.`test3`(`name`,`value3`)VALUES('小明',98);

[柴米油盐酱醋0]

引用 6 楼 ydl1315 的回复:

测试下楼上回复的语句，已经实现了效果，就是比较复杂我一下子还看不明白，汗颜，非常感谢大神的热心帮助！

看着确实有些乱，我自己写的时候也有些乱，只能MYSQL没有分析函数和FULL OUTER JOIN连接。只能用大量的SQL语句实现这些功能。 大概意思为这三个表，NAME字创段分组排序；大概这样 排序号 NAME 成绩 1 小明 100 2 小明 200 1 张三 100 然后TEST1 FULL OUTER JOIN TEST2产生的结果集再FULL OUTER JOIN TEST3 就可以实现想要看到的结果。 你们项目涉及表太多的话写一条SQL语句太繁琐了。

[柴米油盐酱醋0]

弄了下在ORACLE比较好实现，在MYSQL下实现比较困难；我试着写一写，如果不回贴就是没写出来 /笑哭

[柴米油盐酱醋0]

select ifnull(ifnull(hz.name1,hz.name2),hz.name3), hz.value1,hz.name2,hz.value2,hz.name3,hz.value3 from ( SELECT vt12.NAME as name1, vt12.rownum as rownum1, vt12.value1 as value1, vt12.NAME as name11, vt12.name2, vt12.value2, vt12.rownum2, vt12.name22, test3.NAME AS name3, test3.value3, test3.rownum rownum3, test3.name3 as name33 FROM ( SELECT vt1.*, vt2.NAME AS name2, vt2.value2, vt2.rownum AS rownum2, vt2.name22 FROM (select t1.name,t1.value1, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name1 from test1 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt1 left join (select t1.name,t1.value2, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name22 from test2 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt2 on vt1.name=vt2.name and vt1.rownum=vt2.rownum union select vt1.*,vt2.* from (select t1.name,t1.value1, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name1 from test1 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt1 right join (select t1.name,t1.value2, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name1 from test2 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt2 on vt1.name=vt2.name and vt1.rownum=vt2.rownum) vt12 left join (select t1.name,t1.value3, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name3 from test3 as t1,(select @name:=null,@rownum:=1) a order by name asc) test3 on vt12.name=test3.name and vt12.rownum=test3.rownum union select vt12.name,vt12.rownum,vt12.value1,vt12.name,vt12.name2,vt12.value2,vt12.rownum2,vt12.name22,test3.* from (select vt1.*,vt2.name as name2,vt2.value2,vt2.rownum as rownum2,vt2.name22 from (select t1.name,t1.value1, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name1 from test1 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt1 left join (select t1.name,t1.value2, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name22 from test2 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt2 on vt1.name=vt2.name and vt1.rownum=vt2.rownum union select vt1.*,vt2.* from (select t1.name,t1.value1, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name1 from test1 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt1 right join (select t1.name,t1.value2, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name1 from test2 as t1,(select @name:=null,@rownum:=1) a order by name asc) vt2 on vt1.name=vt2.name and vt1.rownum=vt2.rownum) vt12 right join (select t1.name,t1.value3, IF (@name = t1.name,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownum, @name := t1.name as name3 from test3 as t1,(select @name:=null,@rownum:=1) a order by name asc) test3 on vt12.name=test3.name and vt12.rownum=test3.rownum ) hz

[柴米油盐酱醋0]

你看下这是你要的结果吗，写的有些乱；你给的示例有些问题吧，你的VALUE1表只有三条数据；但你在最后生成结果中却生成了6条数据。

[ydl1315]

汇总的生成的test表我输错了，更正下应该是这样的表，非常感谢楼上的热心帮忙，这个需求如果一条语句的话实现还是比较难的，看楼上回复就知道花了不少时间，这里提问只是拿3张表作例子，实际项目中是很多张表的，每天大约2000个产品做测试，生成汇总的报表。我想的笨方法是创建存储过程，创建临时表，统计所有被测品每项测试次数，再循环所有被测品，单个单个进行所有测试项表进行连接并插入到汇总表里。这样可能效率比较低下执行时间长，看看大家有没有更好的方法。

[ydl1315]

汇总的生成的test表我输错了，更正下应该是这样的表，非常感谢楼上的热心帮忙，这个需求如果一条语句的话实现还是比较难的，看楼上回复就知道花了不少时间，这里提问只是拿3张表作例子，实际项目中是很多张表的，每天大约2000个产品做测试，生成汇总的报表。我想的笨方法是创建存储过程，创建临时表，统计所有被测品每项测试次数，再循环所有被测品，单个单个进行所有测试项表进行连接并插入到汇总表里。这样可能效率比较低下执行时间长，看看大家有没有更好的方法。

[ydl1315]

测试下楼上回复的语句，已经实现了效果，就是比较复杂我一下子还看不明白，汗颜，非常感谢大神的热心帮助！