CREATE PROCEDURE Pro_getSubFolder
@ID int
AS
declare @cursorID int,@Name varchar(50)
Declare folderID_cursor Cursor scroll local For
Select id,name from FileFolder where parent_id = @ID
Open folderID_cursor
Fetch Next From folderID_cursor into @cursorID,@Name
While @@Fetch_Status = 0
Begin
Print @Name+chr(13)
if exists(Select id,name from FileFolder where parent_id = @cursorID)
exec Pro_getSubFolder @cursorID
Fetch Next From folderID_cursor into @cursorID,@Name
End
Close folderID_cursor
Deallocate folderID_cursor
GO
@ID int
AS
declare @cursorID int,@Name varchar(50)
Declare folderID_cursor Cursor scroll local For
Select id,name from FileFolder where parent_id = @ID
Open folderID_cursor
Fetch Next From folderID_cursor into @cursorID,@Name
While @@Fetch_Status = 0
Begin
Print @Name+chr(13)
if exists(Select id,name from FileFolder where parent_id = @cursorID)
exec Pro_getSubFolder @cursorID
Fetch Next From folderID_cursor into @cursorID,@Name
End
Close folderID_cursor
Deallocate folderID_cursor
GO
insert @a values(1,0,'a')
insert @a values(2,1,'b')
insert @a values(3,1,'c')
insert @a values(4,2,'d')
insert @a values(5,3,'e')
declare @lev int,@root int
declare @tmp table (id int)
declare @tmp1 table (id int,parent_id int,name varchar(200),lev int)
insert @tmp select id from @a where parent_id=0while exists (select 1 from @tmp)
begin
set @root=(select top 1 id from @tmp)
delete @tmp where id=@root
set @lev=0
insert @tmp1 select *,@lev from @a where id=@root
while exists(select 1 from @a a,@tmp1 b where a.parent_id=b.id and a.id not in (select id from @tmp1))
begin
set @lev=@lev+1
insert @tmp1 select a.*,@lev from @a a,@tmp1 b where a.parent_id=b.id and a.id not in (select id from @tmp1)
end
endselect REPLICATE(' ',lev)+name from @tmp1a
b
c
d
e
你的意思是不是要加一个lev的字段,自己往里写数据?
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))--定义初始值,开始结点
insert @tmp1 select * 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.* 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 bb values (2,'b',1)
insert into bb values (3,'c',1)
insert into bb values (4,'d',2)
CREATE proc Folder
as
declare @FolderID int,@FolderName varchar(50)
declare top1 cursor scroll local for
select id,name from bb where parent_id=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
CREATE proc sub_Folder
@ID int
as
declare @FolderID int,@FolderName varchar(50)
declare sub2 cursor scroll local for
select id,name from bb where parent_id=@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 id from bb where parent_id=@FolderID)
begin
if @@NESTLEVEL<32
exec sub_Folder @FolderID
end
fetch next from sub2 into @FolderID,@FolderName
end
close sub2
deallocate sub2
GO执行:Folder
create procedure Get (@name char(50))
as
create table #work (tcid int, tcpid int)
create table #din(seq int identity ,tcid int ,tcpid int)declare @lvl int ,@curr int
select @lvl=1,@curr=id from TreeClass where name=@nameinsert into #work values(@lvl,@curr)
while(@lvl>0)
begin
if exists (select * from #work where tcid=@lvl)
begin
select top 1 @curr=tcpid from #work where tcid=@lvl insert #din (tcid,tcpid) values (@lvl,@curr) delete #work
where tcid=@lvl and tcpid=@curr
insert #work
select @lvl+1,id
from TreeClass
where parentid=@curr and parentid<>id if(@@rowcount>0) set @lvl=@lvl+1
end else
set @lvl=@lvl-1
end
select replicate(char(9),tcid)+b.name
from #din a join TreeClass b on (a.tcpid=b.id)
order by seqexample:
Get 'a'
--
a
b
c
d
e