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表是每次要插入新的之前，都要把原来的清空，是这样吧？
请稍微详细点，我太笨了。。呵呵
请高手帮下，我是真的想不出来才叫大家帮忙的。 。。谢谢
...全文
请发表友善的回复…
发表回复
linguojin11 2008-12-12
- 打赏
- 举报
我的是2000的，我那本身就没错误，只是我不理解里面的运转。。
apple02180503 2008-12-12
- 打赏
- 举报
----参照这个改改改就行了
Set Nocount On
declare @1 table([F_Dept_ID] int,[F_Parent_DeptID] int,[F_Dept_Name] nvarchar(11))
Insert @1
select 1,N'0',N'公司领导' union all
select 2,N'0',N'办公室' union all
select 3,N'2',N'文秘科' union all
select 4,N'2',N'综合科' union all
select 5,N'2',N'行政管理科' union all
select 6,N'0',N'人事劳资部' union all
select 7,N'6',N'人事管理科' union all
select 8,N'6',N'劳动工资科' union all
select 9,N'6',N'绩效考核办公室' union all
select 10,N'0',N'党群工作部' union all
select 11,N'10',N'组织干部科' union all
select 12,N'10',N'宣传教育科' union all
select 13,N'10',N'企业文化办公室' union all
select 14,N'10',N'离退管理科' union all
select 15,N'0',N'监察部' union all
select 16,N'0',N'审计部' union all
select 17,N'0',N'企业管理部' union all
select 18,N'17',N'生产科' union all
select 19,N'17',N'物资科' union all
select 20,N'17',N'技术中心' union all
select 21,N'17',N'质量监督管理科' union all
select 22,N'17',N'综合科' union all
select 23,N'0',N'资产管理部' union all
select 24,N'23',N'设备科' union all
select 25,N'23',N'配件科' union all
select 26,N'23',N'基建科' union all
select 27,N'23',N'信息管理科' union all
select 28,N'23',N'网络运行科' union all
select 29,N'23',N'综合科' union all
select 30,N'0',N'财务管理部' union all
select 31,N'30',N'财务科' union all
select 32,N'30',N'会计科' union all
select 33,N'30',N'综合科' union all
select 34,N'0',N'烟叶经营部' union all
select 35,N'34',N'省内业务科' union all
select 36,N'34',N'省外业务科' union all
select 37,N'34',N'成品科' union all
select 38,N'34',N'综合科' union all
select 39,N'0',N'安全保卫部' union all
select 40,N'39',N'消防科' union all
select 41,N'39',N'安全技术科' union all
select 42,N'39',N'综合科' union all
select 43,N'0',N'生产加工部' union all
select 44,N'43',N'仓储车间' union all
select 45,N'43',N'分级车间' union all
select 46,N'43',N'打叶车间' union all
select 47,N'43',N'动力车间' union all
select 48,N'43',N'设备维护车间' union all
select 49,N'43',N'综合办公室' union all
select 50,N'0',N'公司工会' union all
select 51,N'43',N'安技办公室' union all
select 52,N'44',N'物流车间' union all
select 53,N'44',N'仓储(初烟保管)' union all
select 54,N'52',N'成品运输组' union all
select 55,N'52',N'打叶运输组' union all
select 56,N'52',N'挑选运输组' union all
select 57,N'52',N'初烟运输组' union all
select 58,N'45',N'挑选车间' union all
select 59,N'58',N'其他' union all
select 60,N'58',N'周转库(解包房\半成品' union all
select 61,N'58',N'机台(挑选机台\精选线' union all
select 62,N'46',N'双6吨线' union all
select 63,N'46',N'12吨线' union all
select 64,N'46',N'锅炉房' union all
select 65,N'46',N'配电室' union all
select 66,N'46',N'水泵房' union all
select 67,N'46',N'空压机' union all
select 68,N'62',N'回潮' union all
select 69,N'62',N'预处理' union all
select 70,N'62',N'打机' union all
select 71,N'62',N'烤机' union all
select 72,N'62',N'打包机' union all
select 73,N'62',N'烤梗线' union all
select 74,N'62',N'烤末线' union all
select 75,N'62',N'其他' union all
select 76,N'63',N'回潮' union all
select 77,N'63',N'预处理' union all
select 78,N'63',N'打机' union all
select 79,N'63',N'烤机' union all
select 80,N'63',N'打包机' union all
select 81,N'63',N'烤梗线' union all
select 82,N'63',N'烤末线' union all
select 83,N'63',N'其他'
If Object_id('tempdb..#') Is Not null
Drop Table #
;With t As
(
Select F_Dept_ID,F_Parent_DeptID,F_Dept_Name,LevelNo=Convert(int,1) From @1 where [F_Parent_DeptID]=0
Union All
Select a.F_Dept_ID,a.F_Parent_DeptID,a.F_Dept_Name,LevelNo=Convert(int,b.LevelNo+1)
From @1 As a
Inner Join t as b On b.F_Dept_ID=a.F_Parent_DeptID
)
Select F_Dept_ID,F_Parent_DeptID,F_Dept_Name,LevelNo
Into #
From t Option(Maxrecursion 0)
Create Nonclustered Index IX_#_DeptID ON #(F_Dept_ID Asc)
Declare
@Join nvarchar(4000),
@Select nvarchar(4000),
@id nvarchar(4000),
@maxlever int,
@clear nvarchar(100)
SELECT @maxlever=max(levelNo) from #
SET @clear='AND '+Char(97+@maxlever)+ '.F_Dept_Name IS NULL'
SET @id='(CASE'
Select @Select=Isnull(@Select+',',',')+Char(96+levelNo)+'.F_Dept_Name As Level'+Rtrim(LevelNo),
@Join=Isnull(@Join+Char(13)+Char(10),'')+'Left Outer Join # As '+Char(96+levelNo)+ ' On '+Char(96+levelNo)+'.F_Parent_DeptID='+Char(95+levelNo)+'.F_Dept_ID'
From # Where LevelNo>1 Group By LevelNo
SELECT @id=@id+' WHEN '+Char(96+levelNo)+ '.F_Dept_Name IS NOT NULL AND '+Char(97+levelNo)+ '.F_Dept_Name IS NULL THEN '+Char(96+levelNo)+ '.F_Dept_ID '
From # Where LevelNo>1 Group By LevelNo
SELECT @id=Replace(@id,@clear,'')+' ELSE a.F_Dept_ID END) F_Dept_ID '
print(@id)
exec('Select '+@id+',a.F_Dept_Name AS Level,a.levelNo As Level'+@Select+' From # As a '+@Join+' where a.levelNo=1 Order By a.levelNo,a.F_Dept_ID')
--select * from #
drop table #
