CREATE proc sub_Folder @ID int as declare @FolderID int,@FolderName varchar(50) declare sub2 cursor scroll local for select FolderID,FolderName from FileFolder where ReferencesID=@ID open sub2 fetch next from sub2 into @FolderID,@FolderName while @@fetch_status = 0 begin print space(@@nestlevel)+cast(@FolderID as varchar(50))+space(1)+@FolderName if exists(select FolderID from FileFolder where ReferencesID=@FolderID) begin if @@NESTLEVEL<32 exec sub_Folder @FolderID end fetch next from sub2 into @FolderID,@FolderName end close sub2 deallocate sub2 GO
CREATE proc Folder as declare @FolderID int,@FolderName varchar(50) declare top1 cursor scroll local for select FolderID,FolderName from FileFolder where ReferencesID=0 open top1 fetch next from top1 into @FolderID,@FolderName while @@fetch_status = 0 begin print cast(@FolderID as varchar(50))+space(1)+@FolderName exec sub_Folder @FolderID fetch next from top1 into @FolderID,@FolderName end close top1 deallocate top1 GO 执行:exec Folder
insert @tmp1 select * from new_tree where parent = @parent insert @tmp3 select * from new_tree where parent = @parent /*循环的次数等于树的深度*/ while exists(select * from @tmp1 where child is not NULL) begin insert @tmp2 select a.* from new_tree a,@tmp1 b where a.parent = b.child /*@tmp2表中存本次查询的层次的所有结点*/ delete from @tmp1 where child is not NULL /*@tmp1表中最终存的是叶子结点*/ insert @tmp1 select * from @tmp2 /*@tmp3表中最保存每次查到的子孙*/ insert @tmp3 select * from @tmp2 delete from @tmp2 end if @mode =0 select * from @tmp3 else select * from @tmp1 set nocount off end goproc_new_tree '1',1 go
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200)) insert @a values(1,0,'中国') insert @a values(2,0,'美国') insert @a values(3,0,'加拿大') insert @a values(4,1,'北京') insert @a values(5,1,'上海') insert @a values(6,1,'江苏') insert @a values(7,6,'苏州') insert @a values(8,7,'常熟') insert @a values(9,6,'南京') insert @a values(10,6,'无锡') insert @a values(11,2,'纽约') insert @a values(12,2,'旧金山')declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int) insert @tmp1 select *,1 from @a where tc_ID=1 while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)) insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1) select * from @tmp1
create table #(Parent_id int,item_id int ) insert into #(Parent_id,item_id) values(1,0) insert into #(Parent_id,item_id) values(2,1) insert into #(Parent_id,item_id) values(3,1) insert into #(Parent_id,item_id) values(4,2) insert into #(Parent_id,item_id) values(5,2) insert into #(Parent_id,item_id) values(6,3) insert into #(Parent_id,item_id) values(7,2) insert into #(Parent_id,item_id) values(8,3) insert into #(Parent_id,item_id) values(9,7) insert into #(Parent_id,item_id) values(10,9)set nocount on declare @ int,@i int set @=2 declare @t table(Parent_id int,item_id int,iv int) declare @t1 table(Parent_id int,item_id int) insert into @t1 select * from # where item_id=2 set @i=1 while exists(select TOP 1 * from @t1) begin insert into @t(Parent_id ,item_id ,iv ) select *,@i from @t1 delete from @t1 insert into @t1 select * from # where item_id in (select Parent_id from @t where iv=@i) set @i=@i+1 end select * from @t drop table # set nocount off
@ID int
as
declare @FolderID int,@FolderName varchar(50)
declare sub2 cursor scroll local for
select FolderID,FolderName from FileFolder where ReferencesID=@ID
open sub2
fetch next from sub2 into @FolderID,@FolderName
while @@fetch_status = 0
begin
print space(@@nestlevel)+cast(@FolderID as varchar(50))+space(1)+@FolderName
if exists(select FolderID from FileFolder where ReferencesID=@FolderID)
begin
if @@NESTLEVEL<32
exec sub_Folder @FolderID
end
fetch next from sub2 into @FolderID,@FolderName
end
close sub2
deallocate sub2
GO
as
declare @FolderID int,@FolderName varchar(50)
declare top1 cursor scroll local for
select FolderID,FolderName from FileFolder where ReferencesID=0
open top1
fetch next from top1 into @FolderID,@FolderName
while @@fetch_status = 0
begin
print cast(@FolderID as varchar(50))+space(1)+@FolderName
exec sub_Folder @FolderID
fetch next from top1 into @FolderID,@FolderName
end
close top1
deallocate top1
GO
执行:exec Folder
go
/*parent对应父亲结点,child对应儿子结点,如果child is NULL则本结点为叶子结点*/
create table new_tree ( parent varchar(80), child varchar(80))
go
insert new_tree values ( '1','2');
insert new_tree values ( '1','3');
insert new_tree values ( '2','4');
insert new_tree values ( '2','5');
insert new_tree values ( '3','6');
insert new_tree values ( '3','7');
insert new_tree values ( '3','8');
insert new_tree values ( '6','9');
insert new_tree values ( '5','10');
insert new_tree values ( '4','11');
insert new_tree values ( '9','12');
insert new_tree values ( '7',NULL);
insert new_tree values ( '8',NULL);
insert new_tree values ( '10',NULL);
insert new_tree values ( '11',NULL);
insert new_tree values ( '12',NULL);drop proc proc_new_tree
go
/*@parent 输入根结点标识,@mode为0 则输出为所有子孙记录,否则输出所有叶子结点*/
create proc proc_new_tree (@parent varchar(80),@mode int =0)
asbegin
set nocount on
/*如果不是SQLSERVER2000可以用临时表*/
declare @tmp1 table ( parent varchar(80), child varchar(80))
declare @tmp2 table ( parent varchar(80), child varchar(80))
declare @tmp3 table ( parent varchar(80), child varchar(80))
insert @tmp1 select * from new_tree where parent = @parent
insert @tmp3 select * from new_tree where parent = @parent /*循环的次数等于树的深度*/
while exists(select * from @tmp1 where child is not NULL)
begin
insert @tmp2 select a.* from new_tree a,@tmp1 b where a.parent = b.child
/*@tmp2表中存本次查询的层次的所有结点*/
delete from @tmp1 where child is not NULL
/*@tmp1表中最终存的是叶子结点*/
insert @tmp1 select * from @tmp2
/*@tmp3表中最保存每次查到的子孙*/
insert @tmp3 select * from @tmp2
delete from @tmp2
end
if @mode =0 select * from @tmp3
else select * from @tmp1
set nocount off
end
goproc_new_tree '1',1
go
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select *,1 from @a where tc_ID=1
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1
insert into #(Parent_id,item_id) values(1,0)
insert into #(Parent_id,item_id) values(2,1)
insert into #(Parent_id,item_id) values(3,1)
insert into #(Parent_id,item_id) values(4,2)
insert into #(Parent_id,item_id) values(5,2)
insert into #(Parent_id,item_id) values(6,3)
insert into #(Parent_id,item_id) values(7,2)
insert into #(Parent_id,item_id) values(8,3)
insert into #(Parent_id,item_id) values(9,7)
insert into #(Parent_id,item_id) values(10,9)set nocount on
declare @ int,@i int
set @=2
declare @t table(Parent_id int,item_id int,iv int)
declare @t1 table(Parent_id int,item_id int)
insert into @t1
select * from # where item_id=2
set @i=1
while exists(select TOP 1 * from @t1)
begin
insert into @t(Parent_id ,item_id ,iv )
select *,@i from @t1
delete from @t1
insert into @t1
select * from # where item_id in (select Parent_id from @t where iv=@i)
set @i=@i+1
end
select * from @t
drop table #
set nocount off