现有表Prod 和Purchase, 一个是生产订单表,一个是采购表
结构分别如下:Prod(生产订单表)
Material P_order Qty
001 PD076 100.00
001 PD077 200.00
002 PD076 200.00
003 PD076 50.00
003 PD078 20.00
主键: Material, P_OrderPurchase(采购订单表)
Material PU_Order Qty
001 PUOD0012 150.00
002 PUOD0013 100.00
002 PUOD0014 200.00
002 PUOD0015 50.00
003 PUOD0016 100.00
主键: Material, P_Order
现在想实现从两个表中检索数据,合并成一个新表,表结果如下:
Material P_order Qty PU_Order qty2
001 PD076 100.00 PUOD0012 150.00
001 PD077 200.00
002 PD076 200.00 PUOD0013 100.00
002 PUOD0014 100.00
002 PUOD0015 200.00
003 PD076 50.00 PUOD0016 100.00
003 PD078 20.00基本需求就是,把两个表合成一个新表,表中包含Prod表和Purchase表的全部记录. 结果中为空的部分解释如下:
比如001 号原材料,因为生产订单有两条记录,但是采购订单只有一条,所以,以两表中记录条数最大的为参照,两条生产记录,一条采购记录,多于出来的那条留空. 同理,002 有三条采购记录,但只有一条生产记录,所以对于002 P_order 和Qty字段部分就留空了.好像用Right 和left join 都做不出来.请大家帮帮忙
结构分别如下:Prod(生产订单表)
Material P_order Qty
001 PD076 100.00
001 PD077 200.00
002 PD076 200.00
003 PD076 50.00
003 PD078 20.00
主键: Material, P_OrderPurchase(采购订单表)
Material PU_Order Qty
001 PUOD0012 150.00
002 PUOD0013 100.00
002 PUOD0014 200.00
002 PUOD0015 50.00
003 PUOD0016 100.00
主键: Material, P_Order
现在想实现从两个表中检索数据,合并成一个新表,表结果如下:
Material P_order Qty PU_Order qty2
001 PD076 100.00 PUOD0012 150.00
001 PD077 200.00
002 PD076 200.00 PUOD0013 100.00
002 PUOD0014 100.00
002 PUOD0015 200.00
003 PD076 50.00 PUOD0016 100.00
003 PD078 20.00基本需求就是,把两个表合成一个新表,表中包含Prod表和Purchase表的全部记录. 结果中为空的部分解释如下:
比如001 号原材料,因为生产订单有两条记录,但是采购订单只有一条,所以,以两表中记录条数最大的为参照,两条生产记录,一条采购记录,多于出来的那条留空. 同理,002 有三条采购记录,但只有一条生产记录,所以对于002 P_order 和Qty字段部分就留空了.好像用Right 和left join 都做不出来.请大家帮帮忙
select * from Prod a full join Purchase b where a.Material=b.Material
go
create table [Prod]([Material] varchar(3),[P_order] varchar(5),[Qty] numeric(5,2))
insert [Prod]
select '001','PD076',100.00 union all
select '001','PD077',200.00 union all
select '002','PD076',200.00 union all
select '003','PD076',50.00 union all
select '003','PD078',20.00
if object_id('[Purchase]') is not null drop table [Purchase]
go
create table [Purchase]([Material] varchar(3),[PU_Order] varchar(8),[Qty] numeric(5,2))
insert [Purchase]
select '001','PUOD0012',150.00 union all
select '002','PUOD0013',100.00 union all
select '002','PUOD0014',200.00 union all
select '002','PUOD0015',50.00 union all
select '003','PUOD0016',100.00-->查询
select
isnull(a.Material,b.Material) as Material,
isnull(a.P_order,'') as P_order,
isnull(ltrim(a.Qty),'') as Qty,
isnull(b.PU_order,'') as PU_order,
isnull(ltrim(b.Qty),'') as Qty2
from (select *,px=(select count(1) from prod where material=t.material and p_order<t.p_order) from Prod t) a
full join (select *,px=(select count(1) from Purchase where material=t.material and PU_order<t.PU_order) from Purchase t) b
on a.Material=b.Material and a.px=b.px
order by material
--测试结果:
/*
Material P_order Qty PU_order Qty2
-------- ------- ----------------------------------------- -------- -----------------------------------------
001 PD076 100.00 PUOD0012 150.00
001 PD077 200.00
002 PD076 200.00 PUOD0013 100.00
002 PUOD0014 200.00
002 PUOD0015 50.00
003 PD076 50.00 PUOD0016 100.00
003 PD078 20.00 (7 行受影响)*/