请问怎么样将四个表JOIN埋一个表.
表1 上海
款号A 1
款号B 1
款号C 1
表2 北京
款号A 1
款号D 1
款号E 1
款号F 1
表3 广州
款号B 1
款号G 2
款号H 2
款号I 2
表4 深圳
款号C 1
款号J 5
款号K 5
款号L 5
结果是
表5 上海 北京 广州 深圳
款号A 1 1
款号B 1 1
款号C 1 1
款号D 1
款号E 1
款号F 1
款号G 2
款号H 2
款号I 2
款号J 5
款号K 5
款号L 5
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 款号