34,590
社区成员
发帖
与我相关
我的任务
分享
下面这例子是联机帮助中的一个类似BOM表的展开例子,不过有几个地方我想了一早上都理解不来,只能求救大家。
create table hierarchy(Parent varchar(20),Child varchar(20))
insert into hierarchy
select 'World', 'Europe' union
select 'World' , 'North America' union
select 'Europe' , 'France' union
select 'France' , 'Paris' union
select 'North America' , 'United States' union
select 'North America' , 'Canada' union
select 'United States' , 'New York' union
select 'United States' , 'Washington' union
select 'New York' , 'New York City' union
select 'Washington' , 'Redmond'
go
--select * from hierarchy
go
CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHIL
go
exec expand 'world'
go
drop table hierarchy
drop proc expand
--------------------------------------
'world'的下级有两个,此时的插入#stack表中应该保存的是('Europe' union ,2) 和 ('North America',2),然后检查@ROWCOUNT>0,继续循环 。那接下来应该怎么处理,#stack中有两个值。如何能对每个子级的下阶进行完全处理后再处理兄弟级。 #stack表是每次要插入新的之前,都要把原来的清空,是这样吧?
请稍微详细点,我太笨了。。呵呵
请高手帮下,我是真的想不出来才叫大家帮忙的。 。。谢谢