/*
转一个邹老大的例子
*/--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
转一个邹老大的例子
*/--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
有2個字段:
dept_no1 dept_no2
11 101
11 102
11 103
101 10111
102 10122
10111 11111
10122 12111
11111 111111 如果輸入:11,
結果為:101,102,103,10111,10122,12111,11111 ,111111
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'pc_TraversalTree'
AND type = 'P')
DROP PROCEDURE pc_TraversalTree
GO
/*
存储过程描述
Ding
通用的遍历树,深度优先
*/CREATE PROCEDURE pc_TraversalTree
@rootID int =-1 --要查询的一组,作为返回表的根
,@SourceSQL Text--数据源的查询语句,MeID,ParentID,Col,Col2列有顺序
,@Splitchar nvarchar(50)=''--显示的层次分隔符号
AS SET NoCount ON
--
declare @top int --当栈顶指针
set @top=0 create table #tmpSource(
AuotID int IDENTITY(1,1),
MeID int,
ParentID int,
Col1 nvarchar(1000),
Col2 nvarchar(1000)
) -- 行ID ,上层行ID,描述1,描述2
exec('Insert #tmpSource (MeID,ParentID,Col1,Col2)'+@SourceSQL)
create table #tmpOutput(
AutoID int IDENTITY (1, 1),
ParentID int ,
MeID int,
[Level] int,
Col1 nvarchar(1000),
Col2 nvarchar(1000)
) create table #Stack(
AutoID int IDENTITY (1, 1),
MeID int,
Parent int,
[level] int
) --根结点进栈
insert #Stack(MeID,[level]) select @rootID,0
declare @StackCount int
set @StackCount=1 --print 'Root is ' + @root declare @last int declare @currentParent int
declare @mylevel int set @currentParent=@rootID if @rootID=-1
begin
insert #Stack (MeID,Parent,[level])
select MeID,@top ,0 from #tmpSource where ParentID IS NULL order by MeID desc end while (@StackCount>0)
begin
--退栈,堆栈表的最后一行为栈顶
--得到当前元素的ID,Parent,level
select @last=Max(AutoID) from #Stack
select top 1 @top=MeID,@currentParent=Parent,@myLevel=[level] from #Stack where AutoID=@last
delete from #Stack where AutoID=@last
--添加行到输出表
insert #tmpOutput (parentID,MeID,[level],Col1,Col2)
select top 1 @currentParent,MeID,@myLevel
,REPLICATE(@SplitChar,@myLevel)+cast(Col1 as nvarchar)
,REPLICATE(@SplitChar,@myLevel)+cast(Col2 as nvarchar)
from #tmpSource where MeID=@top-- select * from #tmpSource where MeID=@top
--将得到所有子节点和当前父节点进栈,
insert #Stack (MeID,Parent,[level])
select MeID,@top ,@myLevel+1 from #tmpSource where ParentID=@top order by MeID desc
set @StackCount=(select Isnull(Count(*),0) from #Stack) end
select * from #tmpOutput
GO/*
调试
*/
EXECUTE pc_TraversalTree
@SourceSQL='SELECT WarehouseId, UpWarehouseId, WhCode, WhName FROM tblWarehouse'
,@Splitchar=' '--select IDENT_CURRENT('tblMateriel_Size')--select MeID from #tmpSource where ParentID=7