mysql如何连接表格

Question

+-------+--------+
| name  | number |
+----------------+
| A     |        |
| B     |        |
| C     |        |
| D     |        |
+----------------++-------+--------+
| name  | number |
+----------------+
| A     |        |
| C     |        |
| D     |        |
| E     |        |
+----------------+如有以上两个表格，为统计其中number字段的值，将其连接成以下的表格样式，应如何操作，如有两个以上的表格，应如何操作？+-------+--------+--------+
| name  |number1 |number2 |
+-------------------------+
| A     |        |        |
| B     |        |        |
| C     |        |        |
| D     |        |        |
| E     |        |        |
+-------------------------+

Answer 1

TB1
¦ name  ¦ number ¦ 
+----------------+ 
¦ A    ¦        ¦ 
¦ B    ¦        ¦ 
¦ C    ¦        ¦ 
¦ D    ¦        ¦ 
+----------------+ 
TB2
¦ name  ¦ number ¦ 
+----------------+ 
¦ A    ¦        ¦ 
¦ C    ¦        ¦ 
¦ D    ¦        ¦ 
¦ E    ¦        ¦ 
+----------------+ TB
+-------+--------+--------+ 
¦ name  ¦number1 ¦number2 ¦ 
+-------------------------+ 
¦ A    ¦        ¦        ¦ 
¦ B    ¦        ¦        ¦ 
¦ C    ¦        ¦        ¦ 
¦ D    ¦        ¦        ¦ 
¦ E    ¦        ¦        ¦ 
+-------------------------+TB2--1,建立临时表
insert into #tmp
select * from TB1 union 
select * from TB2
order by [name],[number]--2,按升序和降序分别取出相关记录
select [name],
         isnull((select top 1 [number] from #tmp where #tmp.[name]=tmp.[name] order by #tmp.[numer]),''),      --升序排列
         isnull((select top 1 [number] from #tmp where #tmp.[name]=tmp.[name] order by #tmp.[numer] desc),'')  --降序排列
from #tmp tmp order by tmp.[name]

Answer 2

select tmp.name,sum(if(t=1,tmp.total,0)) as number1,sum(if(t=2,tmp.total,0)) as number2 from (select t1.name,sum(t1.number) as total,1 as t from table1 t1 group by t1.name union select t2.name,sum(t2.number) as total,2 as t from table2 group by t2.name) tmp