高手赐教：如何抓取到BOM结构倒数最后一层和最后第二层？

[zanglinfeng]

表结构:

表名ps_mstr
字段:ps_par(父料号) ps_comp（子料号） ps_pty_per ....


业务说明：
是产品结构表，比如说成品是FG001，
下面有半成品SFG001,SFG002,SFG003
半成品SFG001下面有中间件WIP001，WIP002;SFG002下有WIP003,WIP004,WIP005;SFG003下面有WIP006，

最后WIP001下面有原材料RAW001,RAW002,WIP002下面有原材料RAW003,WIP003下面有原材料RAW004,RAW005等等。

FG001
|
---------------------------------------------------------------------
| | |
SFG001 SFG002 SFG003
| |
------------------- ---------------------
| | | | |
WIP001 WIP002 WIP003 WIP004 WIP005 WIP006
| | |
RAW001 RAW002 RAW003 RAW004,RAW005 ............

表数据就是
ps_par ps_comp
FG001 SFG001
FG001 SFG002
FG001 SFG003
SFG001 WIP001
SFG001 WIP002
SFG002 WIP003
SFG002 WIP004
SFG002 WIP005
SFG003 WIP006
WIP001 RAW001
WIP001 RAW002
WIP002 RAW003
WIP003 RAW004
WIP003 RAW005


当然这只是一个产品结构，这个粒子只有4层，实际层次可能更多，最多的10多层。

现在需要根据原材料来抓最后一层的成品和最后第二层的半成品，比如说我需要根据RAW001倒推追SFG和FG.



看起来简单的问题，我想了很久不会做！

高手帮忙啊！

[zanglinfeng]

回w75251455，您说的“LZ能确定材料下的所有<路径>的最后一层都是成品~而倒数第二层是半成品吗??那我要找的是不是这二层的产品?”

是的，就是要找所有的这2层。



“字段:ps_par(父料号) ps_comp（子料号） ps_pty_per ....

哪个是自己的号???”

这个有指定的几个号，比如说第一层都是以ps_comp="RAW001"之类的。



“要么父级号加自己~~~要么子级号加自己~~~~~
----------------------
字段:ps_par(父料号) ps_comp（子料号） ps_pty_per ....
你这里可是有三级的意思哦...也行~~那自己的号你也要给一个吧”

这里存在一个重复循环利用的问题，是多对多的关系，一个料号可以做父件，也可以做子件，特别是WIP,SFG一些中间件

[playwarcraft]

--查詢'RAW003'的成品和第2介半成品

select b.* from dbo.f_pid('RAW003') a,ps_mstr b
where level =(select max(level) as level from dbo.f_pid('RAW003') a)
and a.ps_par=b.ps_par

/*如果不要SFG002,SFG003,只要where條件加上 and ps_comp in (select ps_par from dbo.f_pid('RAW003') a)*/
/*
ps_par ps_comp
---------- ----------
FG001 SFG001
FG001 SFG002
FG001 SFG003
*/

[zanglinfeng]

回playwarcraft:

老大，能否讲详细一些呢？ 看不咋懂啊。

[playwarcraft]

如果一個raw對應多個成品,則不能直接max()來取,
可以簡單的
select a.ps_par from dbo.f_pid('RAW003') a
where a.ps_par not in (select ps_comp from ps_mstr)
來唰選出所有的用到'RAW003'的成品,然後再查第2介,就不多說了

[playwarcraft]

簡單來看下面代碼,是不是你要的結果??
create table ps_mstr(ps_par varchar(10),ps_comp varchar(10))
insert into ps_mstr
select 'FG001','SFG001' union all
select 'FG001','SFG002' union all
select 'FG001','SFG003' union all
select 'SFG001','WIP001' union all
select 'SFG001','WIP002' union all
select 'WIP001','RAW001' union all
select 'WIP001','RAW002' union all
select 'WIP002','RAW003' union all
select 'SFG002','WIP003'



Go
Create function f_pid(@ps_comp varchar(10))
returns @t_level table(ps_par varchar(10),level int)
as
begin
declare @level int
set @level=1
insert into @t_level select @ps_comp,@level
while @@rowcount>0
begin
set @level=@level+1
insert into @t_level select a.ps_par,@level
from ps_mstr a,@t_level b
where a.ps_comp=b.ps_par
and b.level=@level-1
end
return
end

GO

select b.* from dbo.f_pid('RAW003') a,ps_mstr b
where level =(select max(level) as level from dbo.f_pid('RAW003') a)
and a.ps_par=b.ps_par
/*
ps_par ps_comp
---------- ----------
FG001 SFG001
FG001 SFG002
FG001 SFG003
*/

drop table ps_mstr
drop function f_pid

[zanglinfeng]

TO playwarcraft：

老大，谢谢了，我来研究一下，一个RAW是对应多个成品的！

研究一下马上给分！

[w75251455]

create table ps_mstr
(ps_par varchar(50), ps_comp varchar(50))
insert into ps_mstr select 'FG001', 'SFG001'
union all select 'FG001', 'SFG002'
union all select 'FG001', 'SFG003'
union all select 'SFG001', 'WIP001'
union all select 'SFG001', 'WIP002'
union all select 'SFG002', 'WIP003'
union all select 'SFG002', 'WIP004'
union all select 'SFG002', 'WIP005'
union all select 'SFG003', 'WIP006'
union all select 'WIP001', 'RAW001'
union all select 'WIP001', 'RAW002'
union all select 'WIP002', 'RAW003'
union all select 'WIP003', 'RAW004'
union all select 'WIP003', 'RAW005'



declare @ph int
set @ph =1
select ps_comp,ps_par+'-'+ps_comp[psph],@ph[ph] into # from ps_mstr where ps_par='FG001'

while(@@rowcount>0)
begin
set @ph=@ph+1

insert into #
select b.ps_comp, a.psph+'-'+b.ps_comp,@ph from
(select * from # where ph=@ph-1)a
join
ps_mstr b
on b.ps_par=a.ps_comp
end


select ps_comp from # a
where not exists(select 1 from # b where CHARINDEX(a.[psph],b.[psph])=1 and [ph]=a.[ph]+1)


drop table #

ps_comp
--------------------------------------------------
WIP004
WIP005
WIP006
RAW001
RAW002
RAW003
RAW004
RAW005

--帅哥~~你是要这个结果吗??

[w75251455]

因为你不写结果`~所以有些地方不明白....

[playwarcraft]

--加2條測試數據
insert into ps_mstr select 'FG002' ,'WIP888'
insert into ps_mstr select 'WIP888','RAW003'

--查詢語句
select b.* from ps_mstr b
where b.ps_par in (select ps_par from dbo.f_pid('RAW003') a where ps_par not in (select ps_comp from ps_mstr))
/*
ps_par ps_comp
---------- ----------
FG001 SFG001
FG001 SFG002
FG001 SFG003
FG002 WIP888
*/

--如果不需要中間2條,加上條件
and b.ps_comp in (select ps_par from dbo.f_pid('RAW003') a)
/*
ps_par ps_comp
---------- ----------
FG001 SFG001
FG002 WIP888
*/

[w75251455]

select ps_comp from # a
where not exists(select 1 from # b where CHARINDEX(a.[psph],b.[psph])=1 and [ph]=a.[ph]+1)
union
select ps_comp from # where psph in
(select substring(psph,1,len(psph)-len(ps_comp)-1) from # a
where not exists(select 1 from # b where CHARINDEX(a.[psph],b.[psph])=1 and [ph]=a.[ph]+1))

ps_comp
--------------------------------------------------
RAW001
RAW002
RAW003
RAW004
RAW005
SFG002
SFG003
WIP001
WIP002
WIP003
WIP004
WIP005
WIP006

（所影响的行数为 13 行）

--还是这个结果??

[w75251455]

--还是这个结果??

select ps_comp from # where ph in(select max(ph) from # union select max(ph)-1 from #)

ps_comp
--------------------------------------------------
WIP001
WIP002
WIP003
WIP004
RAW001
RAW004
RAW005
WIP005
WIP006
RAW002
RAW003

（所影响的行数为 11 行）

[bbbbbb888888]

要是oracle就简单多了...

[zanglinfeng]

我来测试一下，谢谢各位帮忙

[lwk_hlj]

如果是oracle,我马上给出答案，

[sp4]

怎么多对多的关系也这么建表？

[goodluckalong]

mark

[hongyekt]

我以前去一家公司面试的时候就出这个题目，结果~~~~~~~~

[广州接入]

逻辑上很简单。算法也不复杂。
如果是Oracle，其实算法还是一样的，虽然Oracle有Connect By语句，但出来的结果并不能判断某个料品的相对位置。

虽然效率上不一定最好，但用递归是最保险的办法。当然，如果数据比较有规则，也可以考虑其它做法。

[w75251455]

递归是最保险的办法??..............
数据不规则~~递归有用????.................
不要迷信!!!!要会做~才能说..

[zanglinfeng]

我先结贴，再开一个，再给200分问个别的问题。