表合并问题（mysql）

码拜

9年 ago

各位高手，有个问题想问下，把两个表的数据合并成一个表，表数据如下，万分感谢！

解决方案

insert into t2(id,score1,score2)
select t.id,t.score,t1.score from t,t1 where t.id = t1.id
union all
select t1.id,””,t1.score from t1 left join t on t1.id = t.id where t.id is null

由于mysql没有full join，所以只能写2个语句然后合并，且还要去重：
SELECT  t.id ,
t.score ,
t1.score
FROM    t
LEFT JOIN t1 ON t.id = t1.id
UNION
SELECT  t1.id ,
t.score ,
t1.score
FROM    t1
LEFT JOIN t ON t1.id = t.id

select t1.id, t1.score,t2.score from t1 left join t2 on t1.id = t2.id
union
select t2.id, t1.score,t2.score from t1 right join t2 on t1.id = t2.id;

select id,sum(s1),sum(s2)
from(
select id,scroe as s1,0 as s2 from t
union all
select id,0 as s1,scroe as s2 from t1
) t
group by id