请教CSDN的大师，表的合并

[mabuchi]

有3个表 A B C
A表是9月的库存数 B表是10月的库存数 C表是11月的库存数
A B C三个表的结构一样 ：
如 A表（9月库存数）
PARTNO，RESIN，GRADE，USE，DIM，UNIT1，DIM2，TYPE，UNIT，QTY
204-035 7628 A ON 250 LYD 49.5 S RL 1000
206-052 LA02 P ON 125 LYD 49.5 L RK 250
B（10库存数） C（11月库存数）表的结构一样

如何用SQL把表做成这样
PARTNO ，RESIN，GRADE，USE，DIM1，UNIT1，DIM2，TYPE，UNIT，9月库存数，10月库存数，11库存数

请和尚 中国风 ......等等大师帮助，谢谢

[dawugui]

--假设三表partno一一对应。
select a.* , b.QTY [10月库存数], c.QTY [11库存数] 
from a,b,c
where a.PARTNO = b.PARTNO and a.partno = c.PARTNO

[dawugui]

--假设三表partno一一对应。
select a.* , b.QTY [10月库存数], c.QTY [11库存数] 
from a,b,c
where a.PARTNO = b.PARTNO and a.partno = c.PARTNO

--如果不是一一对应，使用full join (其中里面的字段自己写完)
select isnull(m.partno , n.partno) partno , .... , isnull(m.[9月库存数],0) [9月库存数] , isnull(m.[10月库存数],0) [10月库存数] , isnull(n.qty , 0) [11月库存数]
from 
(
select isnull(a.partno , b.partno) partno , ...., isnull(a.QTY,0) [9月库存数] , isnull(b.QTY,0) [10月库存数] from a full join b on a.partno = b.partno
) m
full join c n
on m.partno = n.partno

[mabuchi]

谢谢楼上的兄弟

忘了说， 产品的对应要求 where a.PARTNO = b.PARTNO and a.partno = c.PARTNO 不够



PARTNO，RESIN，GRADE，DIM，UNIT1，DIM2，TYPE，UNIT 都相等才能

还有一些不对应的， 可能A表有的PARTNO B表没有 ，或者尺寸DIM不一样，也不能 合

谢谢

[dawugui]

[Quote=引用 3 楼 mabuchi 的回复:]
谢谢楼上的兄弟

忘了说， 产品的对应要求 where a.PARTNO = b.PARTNO and a.partno = c.PARTNO 不够


PARTNO，RESIN，GRADE，DIM，UNIT1，DIM2，TYPE，UNIT 都相等才能

还有一些不对应的， 可能A表有的PARTNO B表没有 ，或者尺寸DIM不一样，也不能 合

谢谢
[/Quote]
你自己组合关系就行了,方法就是我上面写的了.

[中国风]

用full join 
或用

select 
	PARTNO,RESIN,GRADE,[USE],DIM,UNIT1,DIM2,[TYPE],UNIT,
	[9月库存数]=sum(case when [Month]=9 then QTY else 0 end),
	[10月库存数]=sum(case when [Month]=10 then QTY else 0 end),
	[11库存数]=sum(case when [Month]=11 then QTY else 0 end)
from 
	(select *,[Month]=9 from A
	union all 
	select * ,[Month]=10 from B
	union all 
	select * ,[Month]=11 from C)T
group by PARTNO,RESIN,GRADE,[USE],DIM,UNIT1,DIM2,[TYPE],UNIT

[JmLei]

CREATE TABLE [T04a] (
	[fno] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[fqty] [int] null 
) ON [PRIMARY]
GO
CREATE TABLE [T04b] (
	[fno] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[fqty] [int] null
) ON [PRIMARY]
GO
CREATE TABLE [T04c] (
	[fno] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[fqty] [int] null
) ON [PRIMARY]
GO
insert into t04a select '204-035',1000
insert into t04a select '206-052',250
insert into t04b select '204-035',800
insert into t04b select '208-000',500
insert into t04c select '208-000',300
insert into t04c select '206-052',700

select z.fno,qty_a,qty_b,qty_c from 
(select distinct fno from t04a
union
select distinct fno from t04b
union
select distinct fno from t04c
) as z
full join
(select *,fqty as qty_a from t04a) as a on z.fno=a.fno
full join
(select *,fqty as qty_b from t04b) as b on z.fno=b.fno
full join
(select *,fqty as qty_c from t04c) as c on z.fno=c.fno

[JmLei]

select z.fno,isnull(qty_a,0) as qty_a,isnull(qty_b,0) as qty_b,isnull(qty_c,0) as qty_c from 
(select distinct fno from t04a
union
select distinct fno from t04b
union
select distinct fno from t04c
) as z
full join
(select *,fqty as qty_a from t04a) as a on z.fno=a.fno
full join
(select *,fqty as qty_b from t04b) as b on z.fno=b.fno
full join
(select *,fqty as qty_c from t04c) as c on z.fno=c.fno

fno qty_a qty_b qty_c
---------- ----------- ----------- -----------
204-035 1000 800 0
206-052 250 0 700
208-000 0 500 300

（所影响的行数为 3 行）

[D13ay]

学习下

[人鱼传说]

建议采用roy_88 中国风的答案,简练

[ou_yangpengfei]

学习

[-狙击手-]

///

[filec75]

友情up

[qizhengsheng]

路过学习下