BOM表反查，高分相送！

Question

对于工厂的物料清单（BOM）,我们一般正常的做法是，从上往下查找，树形展开物料清单，从而得出一个产品的中间件及最底层物料，现在我要由一个最底层物料往上查询，得出那些产品的BOM清单中要用到此物料。简单数据表：BOM父结点   子结点   ......(略过）
  A        A1     
  A        A2      
  A        B3        
  B        B1
  B        B2
  B        B3
  B3       D1
  B3       D2从表中我们可以看出:  产品A（包括A1[物料],A2[物料],B3[中间件]);
                     产品B（包括B1[物料],B2[物料],B3[中间件]);
                     中间件B3（包括D1[物料],D2[物料])。现我的问题是：根据物料，查询到哪些产品中使用了它。
    例如：1：根据物料A2，可以查出只有产品A中使用了它；
          2：根据物料D1或D2，可以最终查出只有产品A，B中使用了它们。http://community.csdn.net/Expert/topic/4309/4309888.xml?temp=.3988916
这也是我开的贴，一起给所有分，非常感谢！！！给个具体的思路，最好有源码，分不够在开贴送分。

Answer 1

从单层BOM表中检索产品结构：单层bom表结构：
fatherid(父节点)  |  childid(子节点)  | childnum(用量)
----------------------------------------
中              日             1
中              法              2
中              英              3
俄              加              5
俄              西              9
英              俄              2
英              美              7检索函数：（该函数写在sql server得自定义函数中，给你的程序调用）CREATE  function dbo.f_id(@pcode varchar(15))
returns @re table(childid varchar(15),[level] int,sid varchar(8000),ifyeah varchar(5),num int)
as
begin
declare @l int
set @l=1
insert @re select distinct fatherid,@l,fatherid,'0' as ifyeah,num=1
from singlebom a
where not exists(select * from singlebom where childid=a.fatherid) and fatherid=@pcode
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.childid,@l, ltrim(rTrim(b.sid))+ '>'+a.childid,'1' as ifyeah ,num=a.childnum*b.num
from singlebom a,@re b
where a.fatherid=b.childid and b.[level]=@l-1 and a.childid not in (select                 fatherid as childid from singlebom ) 
         
insert @re select a.childid,@l, ltrim(rTrim(b.sid))+ '>'+a.childid,'0' as ifyeah ,num=a.childnum*b.num
from singlebom a,@re b
where a.fatherid=b.childid and b.[level]=@l-1 and a.childid  in (select                        fatherid as childid from singlebom )  end
return
end调用检索函数：SELECT         [层數] = LEVEL, [貨號] = childid, [是否葉子] = ifyeah, [數量] = num, 
                          [排序] = sid
FROM             f_id('中')
ORDER BY  sid查询结果：
层数 货号 是否叶子 数量 结构
1 中              0 1 中             
2 日              1 1 中    >日             
2 法              1 2 中    >法             
2 英              0 3 中    >英             
3 俄              0 6 中     >英    >俄             
4 加              1 30 中     >英     >俄    >加             
4 西              1 54 中     >英     >俄    >西             
3 美              1 21 中     >英     >美             

Answer 2

谢谢 lovendII(戒情人)，不过不是我想要的结果，还有没有哪位高手发言啊，在线等......
满意马上结贴，多谢了！

Answer 3

提供你思路我认为非常简单，在 SQL SERVER 里面写 TSQL 的 FUNCTION ，归递自身即可反查到无限层次。

Answer 4

请参考以下文章及其相关文章： 
 
    新手来看:如何设计表结构便于treeview显示？ 
    http://access911.net/?kbid;75FABE1E12DC 
 
我这里有具体的 VB 实现代码，你将PARENT ID  改为 CHILD ID 即可反过来向上。我有空给你写成 TSQL 的 FUNCTION 把

Answer 5

这样来理解是不是可以解决你的问题呢？子（父）结点 父（子）结点      ......(略过）
  A1           A         
  A2           A          
  B3           A            
  B1           B      
  B2           B      
  B3           B      
  D1           B3     
  D2           B3     父结点 子结点      ......(略过）
  A1     A         
  A2     A          
  B3     A            
  B1     B      
  B2     B      
  B3     B      
  D1     B3     
  D2     B3

Answer 6

反向BOM是一个普遍的需求
如楼上所言，很多ERP是是加了一个新表或来存反向BOM的

Answer 7

反向统计bom表--zjcxc(邹建) ( ) 信誉：519 
--建立测试环境
Create Table 表(ID varchar(10),PID varchar(10),SEQ varchar(10),NAME varchar(10))
--插入数据
insert into 表
select '0','-1','1','目录' union
select '1','0','1','第一册' union
select '2','0','2','第二册' union
select '3','1','1','第一章' union
select '4','1','2','第二章' union
select '5','3','1','第一节' union
select '6','3','2','第二节' union
select '7','3','3','第三节' union
select '8','4','1','第一节' union
select '9','4','2','第二节' union
select '10','4','3','第三节'
 
--测试语句
go
create function f_cid(@ID varchar(10))
returns @re table(PID varchar(10),[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=1
insert @re select @ID,0,'z'
insert @re select PID,@l,PID
from 表 a
where ID=@ID
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.PID,@l,a.PID+' -> '+b.sid
from 表 a,@re b
where a.ID=b.PID and b.[level]=@l-1
end
update @re set level=@l-level
return
end
go 
 select a.*  from 表 a,dbo.f_cid('10') b where a.id=b.Pid
order by sid desc
--删除测试环境
Drop Table 表
drop function f_cid
/*
ID         PID        SEQ        NAME       
---------- ---------- ---------- ---------- 
10         4          3          第三节
4          1          2          第二章
1          0          1          第一册
0          -1         1          目录*/
--libin_ftsafe(子陌红尘) ( ) 信誉：100 
--生成数据
create table Category(
ID      int,
PID     int,
SEQ     int,
NAME    varchar(10))insert into Category select 0,-1,1,'目录'
insert into Category select 1, 0,1,'第一册'
insert into Category select 2, 0,2,'第二册'
insert into Category select 3, 1,1,'第一章'
insert into Category select 4, 1,2,'第二章'
insert into Category select 5, 3,1,'第一节'
insert into Category select 6, 3,2,'第二节'
insert into Category select 7, 3,3,'第三节'
insert into Category select 8, 4,1,'第一节'
insert into Category select 9, 4,2,'第二节'
insert into Category select 10,4,3,'第三节'--创建存储过程
go
create procedure sp_tree(@ID int)
as
    select * into #t from Category where id = @ID
    
    while @@rowcount<>0
        insert into #t 
        select 
            a.* 
        from 
            Category a,#t b 
        where 
            a.ID = b.PID 
            and
            not exists(select 1 from #t where ID = a.ID) 
    
    select * from #t order by SEQ desc       
--执行存储过程
go
exec sp_tree 10drop procedure sp_tree
drop table Category

Answer 8

以产品-中间件-物料最多有4层为例：
select p,c from pctable
P    C    
---- ---- 
A    A1
A    A2
A    B3
B    B1
B    B2
B    B3
B3   D1
B3   D2
D1   E1
D1   E2CREATE  VIEW dbo.PCTREE
AS
SELECT dbo.pctable.p, dbo.pctable.c AS C1, pctable_1.c AS C2, pctable_2.c AS C3
FROM dbo.pctable pctable_2 RIGHT OUTER JOIN
      dbo.pctable pctable_1 ON pctable_2.p = pctable_1.c RIGHT OUTER JOIN
      dbo.pctable ON pctable_1.p = dbo.pctable.cSELECT * FROM PCTREE
p    C1   C2   C3   
---- ---- ---- ---- 
A    A1   NULL NULL
A    A2   NULL NULL
A    B3   D1   E1
A    B3   D1   E2
A    B3   D2   NULL
B    B1   NULL NULL
B    B2   NULL NULL
B    B3   D1   E1
B    B3   D1   E2
B    B3   D2   NULL
B3   D1   E1   NULL
B3   D1   E2   NULL
B3   D2   NULL NULL
D1   E1   NULL NULL
D1   E2   NULL NULLCREATE VIEW dbo.pcview
as
SELECT p as '产品',isnull(c3,isnull(c2,c1)) AS '物料' FROM PCTREE
where p not in (select c from pctable)select * from pcview
产品   物料   
---- ---- 
A    A1
A    A2
A    E1
A    E2
A    D2
B    B1
B    B2
B    E1
B    E2
B    D2求用到物料B2的产品：
select 产品 from pcview where 物料 like 'B2'