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 , ' ', 1
2 , BN , B , 1 , E , 'EN', 1
2 , BN , B , 1 , F , '' , 1
3 , EN , E , 1 , G , '' , 1
4 , 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
------解决方案--------------------
没看明白。。。
------解决方案--------------------
- SQL code
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_sorderselect 1 , 'AN' , 'A ', 1 , 'B' , 'BN' , 1 union allselect 1 , 'AN' , 'A' , 1 , 'C' , ' ', 1 union allselect 1 , 'AN' , 'A' , 1 , 'D' , ' ', 1 union allselect 2 , 'BN' , 'B' , 1 , 'E' , 'EN', 1 union allselect 2 , 'BN' , 'B' , 1 , 'F' , '' , 1 union allselect 3 , 'EN' , 'E' , 1 , 'G' , '' , 1 union allselect 4 , 'AK' , 'A' , 1 , 'B' , 'EK' , 1 union allselect 5 , 'EK' , 'B' , 1 , 'J' , '' , 1select * 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 ctewhere dbomno=''order by billid,bomno,materialid/*billid bomno materialid mqty elemgid dbomno dqty----------- ----- ---------- ----------- ------- ------ -----------1 AN A 1 C 11 AN A 1 D 11 AN A 1 F 11 AN A 1 G 14 AK A 1 J 1*/