日期:2014-05-18  浏览次数:20652 次

SQL如何写三张表 合并成一张表


大家好,想请教一下如何将三张表合并成一张表
三张表分别为
PP1
Product_name num1
A 2
B 3
C 6
D 7

PP2
Product_name num2
E 3
A 2
B 1

PP3
Product_name num3
F 2
G 5
C 8

SQL里如何写语句成为下面的表
PP

Product_name num1 num2 num3
A 2 2 0
B 3 1 0
C 6 0 8
D 7 0 0
E 0 3 0
F 0 0 2
G 0 0 5



------解决方案--------------------
SQL code

create table PP1(Product_name varchar(3),num1 int)
create table PP2(Product_name varchar(3),num2 int)
create table PP3(Product_name varchar(3),num3 int)

insert into PP1
select 'A', 2 union all
select 'B', 3 union all
select 'C', 6 union all
select 'D', 7

insert into PP2
select 'E', 3 union all
select 'A', 2 union all
select 'B', 1

insert into PP3
select 'F', 2 union all
select 'G', 5 union all
select 'C', 8


select coalesce(a.Product_name,b.Product_name,c.Product_name) 'Product_name',
       isnull(a.num1,0) 'num1',
       isnull(b.num2,0) 'num2',
       isnull(c.num3,0) 'num3'
into PP       
from PP1 a
full join PP2 b on a.Product_name=b.Product_name
full join PP3 c on isnull(a.Product_name,b.Product_name)=c.Product_name

select * from PP

Product_name num1        num2        num3
------------ ----------- ----------- -----------
A            2           2           0
B            3           1           0
C            6           0           8
D            7           0           0
E            0           3           0
F            0           0           2
G            0           0           5

(7 row(s) affected)