MSSQL2000
视图vip_goodsbom 为多版本BOM
视图vip_sorder为订单
求订单关联BOM展开物料视图清单ID,版本号,父件id,父件数量,子件ID,子件版本号,子件数量
billid,bomno,materialid,mqty,elemgid,dbomno,dqty
1 , AN , A , 1 , B , BN , 1
1 , AN , A , 1 , C , ' ', 1
1 , AN , A , 1 , D , ' ', 12 , BN , B , 1 , E , 'EN', 1
2 , BN , B , 1 , F , '' , 13 , EN , E , 1 , G , '' , 14 , AK , A , 1 , B , EK , 1
5 , EK , B , 1 , J , '' , 1
当视图中订单vip_sorder
materialid,bomno
A ,AN
则查询展开结果
billid,bomno,materialid,mqty,elemgid,dbomno,dqty
1 , AN , A , 1 , C , ' ', 1
1 , AN , A , 1 , D , ' ', 1
1 , AN , A , 1 , E , ' ', 1
1 , AN , A , 1 , F , '' , 1
当视图中订单vip_sorder
materialid,bomno
A ,Ak
则查询展开结果
billid,bomno,materialid,mqty,elemgid,dbomno,dqty
4 , AK , A , 1 , J , '' , 1
当视图中订单vip_sorder
materialid,bomno
A ,AN
A ,AK
视图vip_goodsbom 为多版本BOM
视图vip_sorder为订单
求订单关联BOM展开物料视图清单ID,版本号,父件id,父件数量,子件ID,子件版本号,子件数量
billid,bomno,materialid,mqty,elemgid,dbomno,dqty
1 , AN , A , 1 , B , BN , 1
1 , AN , A , 1 , C , ' ', 1
1 , AN , A , 1 , D , ' ', 12 , BN , B , 1 , E , 'EN', 1
2 , BN , B , 1 , F , '' , 13 , EN , E , 1 , G , '' , 14 , AK , A , 1 , B , EK , 1
5 , EK , B , 1 , J , '' , 1
当视图中订单vip_sorder
materialid,bomno
A ,AN
则查询展开结果
billid,bomno,materialid,mqty,elemgid,dbomno,dqty
1 , AN , A , 1 , C , ' ', 1
1 , AN , A , 1 , D , ' ', 1
1 , AN , A , 1 , E , ' ', 1
1 , AN , A , 1 , F , '' , 1
当视图中订单vip_sorder
materialid,bomno
A ,Ak
则查询展开结果
billid,bomno,materialid,mqty,elemgid,dbomno,dqty
4 , AK , A , 1 , J , '' , 1
当视图中订单vip_sorder
materialid,bomno
A ,AN
A ,AK
declare @vip_sorder table(billid int,bomno varchar(2),materialid varchar(1),mqty int,elemgid varchar(1),dbomno varchar(2),dqty int)
insert into @vip_sorder
select 1 , 'AN' , 'A ', 1 , 'B' , 'BN' , 1 union all
select 1 , 'AN' , 'A' , 1 , 'C' , ' ', 1 union all
select 1 , 'AN' , 'A' , 1 , 'D' , ' ', 1 union all
select 2 , 'BN' , 'B' , 1 , 'E' , 'EN', 1 union all
select 2 , 'BN' , 'B' , 1 , 'F' , '' , 1 union all
select 3 , 'EN' , 'E' , 1 , 'G' , '' , 1 union all
select 4 , 'AK' , 'A' , 1 , 'B' , 'EK' , 1 union all
select 5 , 'EK' , 'B' , 1 , 'J' , '' , 1
select * from @vip_sorder;with cte as
(
select billid,bomno,materialid,mqty,elemgid,dbomno,dqty from @vip_sorder where materialid='A' and bomno in ('AN','AK')
union all
select cte.billid,cte.bomno,cte.materialid,t.mqty,t.elemgid,t.dbomno,t.dqty from @vip_sorder t,cte
where cte.dbomno=t.bomno
)select * from cte
where dbomno=''
order by billid,bomno,materialid
/*
billid bomno materialid mqty elemgid dbomno dqty
----------- ----- ---------- ----------- ------- ------ -----------
1 AN A 1 C 1
1 AN A 1 D 1
1 AN A 1 F 1
1 AN A 1 G 1
4 AK A 1 J 1
*/
前提: 1\有物料清单 视图vip_goodsbom 为多版本BOM
2\视图vip_sorder为订单求根据订单内容展开所需物料视图