declare @temp table (id int)insert @temp values (1)while exists ( select col3 from 表 where col1 in (select id from @temp) and col3 not in (select id from @temp) ) insert @temp select col3 from 表 where col1 in (select id from @temp) and col3 not in (select id from @temp) ---把所有子节点插入@tempselect * from 表 where col3 in (select col3 from @temp)
CREATE function fun_col1(@ID int) returns varchar(5000) as begin declare @col3 int,@col1 varchar(50),@Str varchar(50) set @Str = '' select @col1 = col1,@col3 = col3 from TableName where col1 = @ID and col3<>0 set @Str = @Str+@col1 + '\' if exists(select col1 from Folder where col1 = @col3 and col3<>0) begin select @col1 = col1 from TableName where col1 = @col3 and col3<>0 set @Str = @col1 + '\' + @Str end return @Str end 执行:select dbo.fun_col1(col1) from TableName where col1=......
select col3 from 表
where col1 in (select id from @temp)
and col3 not in (select id from @temp)
)
insert @temp
select col3 from 表
where col1 in (select id from @temp)
and col3 not in (select id from @temp)
---把所有子节点插入@tempselect * from 表
where col3 in (select col3 from @temp)
returns varchar(5000)
as
begin
declare @col3 int,@col1 varchar(50),@Str varchar(50)
set @Str = ''
select @col1 = col1,@col3 = col3 from TableName where col1 = @ID and col3<>0
set @Str = @Str+@col1 + '\'
if exists(select col1 from Folder where col1 = @col3 and col3<>0)
begin
select @col1 = col1 from TableName where col1 = @col3 and col3<>0
set @Str = @col1 + '\' + @Str
end
return @Str
end
执行:select dbo.fun_col1(col1) from TableName where col1=......