寫了一個展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
---------- ----------
CA
CB
DA
DB
EA
EB
FA
FB
FH
*/
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
BEGIN
SET @Level = @Level + 1
DELETE A
FROM # A, #re B
WHERE A.id = B.id
AND B.Level = @Level - 1
INSERT #re(
id, Level,
child_part, parent_part)
SELECT
A.id, @Level,
A.child_part, B.parent_part
FROM # A, #re B
WHERE A.parent_part = B.child_part
AND B.Level = @Level - 1
END
-- 显示结果
SELECT
child_part, parent_part
FROM #re
ORDER BY 1, 2
GO
drop table pl_bom, #re, #
[解决办法]
收藏...
[解决办法]
顶沟沟一脚！~~HOHO
[解决办法]
收藏...
[解决办法]
--如果是2005的话，可以试试这个
declare @table table (parent_part varchar(10),child_part varchar(10))
insert into @table
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 ';
with result(child_part,parent_part,level)
as
(
select a.child_part,a.parent_part,0 as level
from @table a
where not exists(select 1 from @table where child_part=a.parent_part)
union all
select a.child_part,b.parent_part,b.level+1
from @table a inner join result b on a.parent_part=b.child_part
)
select * from result order by child_part,parent_part
[解决办法]
传说中的高手聚会？
[解决办法]
接分的来啦
[解决办法]
接分
[解决办法]
抓奶龙抓手来接分。哈哈
[解决办法]
偶是来接分的,不参与研究工作
[解决办法]
Mark Mark Mark
receive 分
[解决办法]
支持!
[解决办法]
支持！！
[解决办法]
为了我的学习,为了我的未来,为了全国的GDP,为了我的分.感谢一下楼主.
[解决办法]
gh
[解决办法]
mark~
好有技术含量的问题，竟然在非技术区～
