join 表
请问怎么样将四个表JOIN埋一个表.
表1上海
款号A1
款号B1
款号C1
表2北京
款号A1
款号D1
款号E1
款号F1
表3广州
款号B1
款号G2
款号H2
款号I2
表4深圳
款号C1
款号J5
款号K5
款号L5
结果是
表5上海北京广州深圳
款号A11
款号B11
款号C11
款号D1
款号E1
款号F1
款号G2
款号H2
款号I2
款号J5
款号K5
款号L5
SQL?JOIN
[解决办法]

select 
款号,
sum(case when Type=1 then 数量 else 0 end) as 上海,
sum(case when Type=2 then 数量 else 0 end) as 北京,
sum(case when Type=3 then 数量 else 0 end) as 广州,
sum(case when Type=4 then 数量 else 0 end) as 深圳
from 
(select 款号,数量,1 as Type from 表1
union all
select 款号,数量,2 as Type from 表2
union all
select 款号,数量,3 as Type from 表3
union all
select 款号,数量,4 as Type from 表4
)as t
group by 款号

[解决办法]
select
款号,isnull(sum(case when Type=1 then 数量 else 0 end),0) as 上海,
isnull(sum(case when Type=2 then 数量 else 0 end),0) as 北京,
isnull(sum(case when Type=3 then 数量 else 0 end),0) as 广州,
isnull(sum(case when Type=4 then 数量 else 0 end),0) as 深圳
from
(select 款号,数量,1 as Type from 表1
union all
select 款号,数量,2 as Type from 表2
union all
select 款号,数量,3 as Type from 表3
union all
select 款号,数量,4 as Type from 表4
)as t
group by 款号