递归问题：BOM怎么实现

Question

现象描述：数据库中建有存放BOM的表：BOMMC  主要字段：主件品号，  子件品号
        所有的父阶与子阶都放在这个表中，索引进通过主件品号可以查到相关的子件品号，反之也一样。其中的阶层不一不定期的，有可以5层，也可能是8层。
  例子：
    主件品号   子件品号
     A            B
     B            C
     C            D那么实现查询时显示结果为：
    A
      B
        C
          D
  要求：最好能通过递归的方法，使用SQL语句实现。  或能过别的方法，同样要用SQL实现。

Answer 1

  if OBJECT_ID('tb') is not null
  drop table tb
  go
  create table tb(主件品号 varchar(2),子件品号 varchar(2))
  insert into tb
  select  'A', 'B' union all
  select  'B', 'C' union all
  select  'C', 'D'
  
  
  declare @num varchar(2)
  set @num='A'
  ;with t as 
  (
  select 主件品号 from tb where 主件品号=@num
  union all
  select 子件品号 from tb a join t on a.主件品号=t.主件品号
  )
  select * from t
  
  
主件品号
A
B
C
D

Answer 2

--tryif object_id('tb') is not null
   drop table tb
go
create table tb(主件品号 nvarchar(10),子件品号 nvarchar(10))
insert into tb select 'A','B'
     union all select 'B','C'
     union all select 'C','D'
go
if object_id('c_f') is not null
   drop function c_f
go
create function c_f
(@id nvarchar(10))
returns @a table(id nvarchar(10),level int)
as
begin
declare @level int
set @level=1
insert into @a select  @id,@level
while @@rowcount>0
begin
set @level=@level+1
insert into @a 
        select  b.子件品号,@level from @a a join tb b 
                    on a.id=b.主件品号 and a.level=@level-1end
return
end
goselect id from  dbo.c_f('A')/*id
----------
A
B
C
D(4 行受影响)*/

Answer 3

上面语句确实能实现递归，但是显示的顺序不对，如果下面的子件有多个层，多个品号，怎样显示树形结构？
如：create table tb(主件品号 nvarchar(10),子件品号 nvarchar(10))
insert into tb select 'A','B'
     union all select 'B','C'
     union all select 'C','D'
     union all select 'C','D1'
     union all select 'A','F'
     union all select 'F','F1'
     union all select 'F','F2'
     union all select 'B','B1'
怎样显示树形结构的正确顺序，怎样在SQL中实现？

Answer 4

上面语句确实能实现递归，但是显示的顺序不对，如果下面的子件有多个层，多个品号，怎样显示树形结构？
如：create table tb(主件品号 nvarchar(10),子件品号 nvarchar(10))
insert into tb select 'A','B'
     union all select 'B','C'
     union all select 'C','D'
     union all select 'C','D1'
     union all select 'A','F'
     union all select 'F','F1'
     union all select 'F','F2'
     union all select 'B','B1'
怎样显示树形结构的正确顺序，怎样在SQL中实现？因为要再根据得出的结果做报表，必须在SQL中实现。请各位帮帮手

Answer 5

我找到了部分数据，但是还是有点问题，请教育下：
--测试数据
DECLARE @t  TABLE (ID nvarchar(20),PID nvarchar(20),Name nvarchar(20))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
UNION ALL SELECT '006','005','清远市1'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID nvarchar(20),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
    FROM @t a,@t_Level b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name,a.ID,a.PID
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort/|--山东省 001 NULL
  |--烟台市 002 001
    |--招远市 004 002
  |--青岛市 003 001
|--四会市 005 NULL
  |--清远市 006 005
  |--清远市1 006 005
  |--清远市 006 005
  |--清远市1 006 005
    |--小分市 007 006
    |--小分市 007 006
怎样去掉重复记录？

Answer 6

按BOM路徑排序就可以了
WITH EmpCTE(assm,assm_no, part_no, lvl,per,eng_per, sortcol)
AS
(
SELECT assm_no,assm_no, part_no, 0,qty_per,qty_per,
cast(assm_no+' \ '+part_no as varchar(8000))
FROM dbo.engbomm
WHERE assm_no in('ATKQC25292-US')
UNION ALL
SELECT assm,E.assm_no, E.part_no, M.lvl+1,qty_per,qty_per*eng_per,
cast(sortcol  + ' \ '+ cast(E.part_no as varchar(100))as varchar(8000))
FROM engbomm AS E JOIN EmpCTE AS M ON E.assm_no = M.part_no
)
SELECT SPACE(lvl*4)+part_no,* FROM EmpCTE  ORDER BY sortcol

Answer 7

八楼的兄弟  麻烦说明 ssm,assm_no, part_no, lvl,per,eng_per, sortcol  这些字段代表的意思？
或者 将engbomm 的结构说明。
不好意思， 有点看不懂啊在此谢谢先!!!