SQL 目录树

来源：互联网发布：网络传真机好用吗编辑：程序博客网时间：2024/05/20 04:13

--初始化

create table Dept(ID int,ParentID int,msg varchar(20))
insert into Dept select 1,0,null
insert into Dept select 2,1,null
insert into Dept select 3,1,null
insert into Dept select 4,2,null
insert into Dept select 5,3,null
insert into Dept select 6,5,null
insert into Dept select 7,6,null
--初始化
--测试
declare @str varchar(5000)
set @STR=''
select @str=@str+','+convert(varchar(20),id) from dbo.GetChild('3')
PRINT right(@STR,len(@str)-1)

--测试

--函数
Create function [dbo].[GetChild](@ID varchar(10))
returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
    declare @i int
    set @i = 1
    insert into @t select @ID,@ID,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
    insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
    while @@rowcount<>0
    begin
       set @i = @i + 1
       insert into @t
       select
           a.ID,a.ParentID,@i
       from
           Dept a,@t b
       where
           a.ParentID=b.ID and b.Level = @i-1
    end
    return
end

--函数