大家幫忙看看有沒有錯? 或者有米好辦法?
發現之前寫的程序在展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