假如有三张表table1: 字段i,j,a,b,ctable2: 字段i,j,d,e,f,gtable3: 字段i,j,h,k,z,y三张表主键都是(i,j)需要新建一张表,合并这三张表的字段和值。新建的表结构应如下:
i,j,a,b,c,d,e,f,g,h,k,z,y
如值如下:
table1:
1,2,abc,egeg,kjj
11,22,gwgw,wgwg,jyztable2:
1,2,wgwg,gwgwg,www
2,3,a123,213g,ehgj,5g
11,22,23h,gh23,323,83kgtable3:
1,2,1jg2,132keg,45,1jg
2,3,hg33,3hjt,j32,2jg
122,12,h3,h1,hg3,s98
由于源表几张表的主键字段一样。不知道合并时,如果主键值相同,怎么处理主键值。
还有,多张表合并不知道怎么写SQL语句。
我不是做数据库的
忘高手帮帮忙,很急!3Q
i,j,a,b,c,d,e,f,g,h,k,z,y
如值如下:
table1:
1,2,abc,egeg,kjj
11,22,gwgw,wgwg,jyztable2:
1,2,wgwg,gwgwg,www
2,3,a123,213g,ehgj,5g
11,22,23h,gh23,323,83kgtable3:
1,2,1jg2,132keg,45,1jg
2,3,hg33,3hjt,j32,2jg
122,12,h3,h1,hg3,s98
由于源表几张表的主键字段一样。不知道合并时,如果主键值相同,怎么处理主键值。
还有,多张表合并不知道怎么写SQL语句。
我不是做数据库的
忘高手帮帮忙,很急!3Q
select i,j,max(a),max(b),max(c),max(d),max(e),max(f),max(g),max(h),max(k),max(z),max(y)
from (
select i,j,a,b,c,null as d,null as e,null as f,null as g,null as h,null as k,null as z,null as y
from table1
union all
select i,j,null as a,null as b,null as c,d,e,f,g,null as h,null as k,null as z,null as y
from table2
union all
select i,j,null as a,null as b,null as c,null as d,null as e,null as f,null as g,h,k,z,y
from table3
) t
group by i,j
插入table1: 字段i,j,a,b,c
insert into new_table(i,j,a,b,c) on duplicate key update a=a,b=b,c=c
其他的以此类推,不明白了搜索一下insert into on duplicate key
实际中不能列出具体的字段
因为字段非常多
不可能挨个列出
有的表有几百个字段
有的表几十个字段
不过合并所有表的字段后
字段数不会超过MYSQL的最大字段数1000只能是选择所有的那种形式
FROM (SELECT DISTINCT *
FROM (SELECT i, j
FROM Table1
UNION ALL
SELECT i, j
FROM Table2
UNION ALL
SELECT i, j
FROM Table3) T0) T1 LEFT OUTER JOIN
table1 ON table1.i = T1.i AND table1.j = T1.j LEFT OUTER JOIN
table2 ON table2.i = T1.i AND table2.j = T1.j LEFT OUTER JOIN
Table3 ON Table3.i = T1.i AND Table3.j = T1.j
SELECT T1.i,T1,j, table1.(除了Key的所有), table2.(除了Key的所有), Table3.(除了Key的所有)
FROM (SELECT DISTINCT *
FROM (SELECT i, j
FROM Table1
UNION ALL
SELECT i, j
FROM Table2
UNION ALL
SELECT i, j
FROM Table3) T0) T1 LEFT OUTER JOIN
table1 ON table1.i = T1.i AND table1.j = T1.j LEFT OUTER JOIN
table2 ON table2.i = T1.i AND table2.j = T1.j LEFT OUTER JOIN
Table3 ON Table3.i = T1.i AND Table3.j = T1.j
DELIMITER $$USE `ee`$$DROP PROCEDURE IF EXISTS `hb`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `hb`(b1 CHAR(20),b2 CHAR(20),b3 CHAR(20))
BEGIN
DECLARE dd1,dd2,dd3,dd4,dd5,dd6 VARCHAR(500);
DECLARE done INT DEFAULT 0;
DECLARE v_a INT;
DECLARE cur1 CURSOR FOR
SELECT DISTINCT a.COLUMN_NAME FROM information_schema.COLUMNS a WHERE TABLE_SCHEMA='ee' AND TABLE_NAME IN(b1,b2,b3) ORDER BY COLUMN_NAME;
DECLARE cur2 CURSOR FOR SELECT a.TABLE_NAME,a.COLUMN_NAME FROM information_schema.COLUMNS a WHERE TABLE_SCHEMA='ee' AND TABLE_NAME IN(b1,b2,b3) ORDER BY TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;SELECT b1,b2,b3;
SET dd3=b1;
SET dd4='create table newtt( ';
OPEN cur1;
FETCH cur1 INTO dd1;
WHILE done=0 DO
SET dd4=CONCAT(dd4,dd1,' varchar(10),');
FETCH cur1 INTO dd1;
END WHILE;
SET dd4=CONCAT(LEFT(dd4,LENGTH(dd4)-1),')');
SELECT dd4;
SET done=0;
SET dd5=b1;
SET dd4='insert into newtt(';
SET dd6='select ';
OPEN cur2;
FETCH cur2 INTO dd2,dd3;
WHILE done=0 DO
IF dd5<>dd2 THEN
SET dd4=CONCAT(LEFT(dd4,LENGTH(dd4)-1),') ',LEFT(dd6,LENGTH(dd6)-1),' from ',dd5);
SELECT dd4;
SET dd4='insert into newtt(';
SET dd5=dd2;
SET dd6='select ';
END IF;
SET dd4=CONCAT(dd4,dd3,',');
SET dd6=CONCAT(dd6,dd3,',');
FETCH cur2 INTO dd2,dd3;
END WHILE;
SET dd4=CONCAT(LEFT(dd4,LENGTH(dd4)-1),') ',LEFT(dd6,LENGTH(dd6)-1),' from ',dd5);
SELECT dd4;
END$$DELIMITER ;
自行修改,动态执行DD4
不同表的字段取sum,进行算术运算。这个用一条SQL语句怎么做??
比如:
sum(table1.a)+sum(table2.b)*(1+sum(table3.c))
sum()括弧中是表名和字段名
语句是在JAVA程序中拼接的,想写成一条SQL语句。其中,程序所要做的是拼存储过程。因为SQL语句交给存储过程去执行比较快。
反之,每个sum的结果交给JAVA去和MYSQL交互得到结果,性能很差。不堪设想
select (select sum(a) from t1),(select sum(b) from t2)*(select sum(c) from t) from dual