数据库结果集根据某一字段合并

[lizhzh_2001]

两个结果集
（1）table score_s
姓名name | 科学science
张三 | 80
李四 | 60
（2）table score_w
姓名 name | 写作 writing
张三 | 75
王五 | 90

合并成

姓名 name | 科学science | 写作 writing
张三 | 80 | 75
李四 | 60 | 0
王五 | 0 | 90

sql应该如何写?

[ayzen1988]

select a.name,if(a.science, a.science, 0)science,if(b.writing, b.writing, 0)writing from table score_s a left join table score_w b on a.name = b.name
union all
select a.name,if(a.science, a.science, 0)science,if(b.writing, b.writing, 0)writing from table score_s a right join table score_w b on a.name = b.name where a.name is null;

[ACMAIN_CHM]

select 姓名name,sum(科学science),sum(写作 writing)
from (
	select 姓名name , 科学science, 0 as 写作 writing from score_s
	union all
	select 姓名name , 0, 写作 writing from score_w
) t
group by 姓名name

[qq_17367431]

SELECT name,sum(science) as science,sum(writing) as writing FROM
(SELECT name,science,0 as writing FROM score_s
UNION 
SELECT name,0 as science,writing FROM score_w) t
GROUP BY name