两个结果集
(1)table score_s
姓名name | 科学science
张三 | 80
李四 | 60
(2)table score_w
姓名 name | 写作 writing
张三 | 75
王五 | 90合并成姓名 name | 科学science | 写作 writing
张三 | 80 | 75
李四 | 60 | 0
王五 | 0 | 90sql应该如何写?
(1)table score_s
姓名name | 科学science
张三 | 80
李四 | 60
(2)table score_w
姓名 name | 写作 writing
张三 | 75
王五 | 90合并成姓名 name | 科学science | 写作 writing
张三 | 80 | 75
李四 | 60 | 0
王五 | 0 | 90sql应该如何写?
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;
from (
select 姓名name , 科学science, 0 as 写作 writing from score_s
union all
select 姓名name , 0, 写作 writing from score_w
) t
group by 姓名name
(SELECT name,science,0 as writing FROM score_s
UNION
SELECT name,0 as science,writing FROM score_w) t
GROUP BY name