寫了一個展bom top 介的sp,不知道邏輯上有沒有問題,幫忙看下哈
大家幫忙看看有沒有錯?   或者有米好辦法?
發現之前寫的程序在展bom的top介上花了好多時間,ms用這個展,速度還可以接受.

--pl_bom表:
create   table   pl_bom(parent_part   varchar(10),child_part   varchar(10))
insert   into   pl_bom
select   'A ', 'C '   union   all
select   'B ', 'C '   union   all
select   'C ', 'D '   union   all
select   'D ', 'E '   union   all
select   'D ', 'F '   union   all
select   'H ', 'F '  
GO
/*   A,B,H是top介,本身是top的就不展了   */

select   child_part   as   part   ,parent_part   as   parent  
into   #direct   from   pl_bom   (nolock)

declare     @lop     int
set   @lop=1
while(@lop> 0)
begin
    select       part   as   part   ,parent   as   parent,   parent_part   as   direct  
    into   #tmp  
    from   pl_bom   (nolock)   ,   #direct  
    where     parent=child_part

    delete     #direct  
    from     #direct   as   a   ,#tmp   as   b  
    where   a.part=b.part   and   a.parent=b.parent

    set   @lop=@@rowcount  

    insert   into   #direct   select   distinct   part,direct   from   #tmp

    drop   table   #tmp
end

select   *   from   #direct   order   by   part

/*
part               parent          
----------   ----------  
C A
C B
D A
D B
E A
E B
F A
F B
F H

*/

drop   table   #direct,pl_bom



------解决方案--------------------
沙发一个
------解决方案--------------------
路过，支持
------解决方案--------------------
1
------解决方案--------------------
2
------解决方案--------------------
5
------解决方案--------------------
1
------解决方案--------------------
--pl_bom表:
create table pl_bom(parent_part varchar(10),child_part varchar(10))
insert into pl_bom
select 'A ', 'C ' union all
select 'B ', 'C ' union all
select 'C ', 'D ' union all
select 'D ', 'E ' union all
select 'D ', 'F ' union all
select 'H ', 'F '
GO

-- 因为要删除数据, 所以不能用原始表, 用个临时表
SELECT
id = IDENTITY(int, 1,1 ), child_part, parent_part
INTO #
FROM pl_bom

-- 从顶往下展
DECLARE @Level int
SET @Level = 1
SELECT
id = id * 1, Level = @Level,
child_part, parent_part
INTO #re
FROM # A
WHERE NOT EXISTS(
SELECT * FROM #
WHERE child_part = A.parent_part)
WHILE @@ROWCOUNT > 0