--创建一个查询函数
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert parent_id,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1
and a.parent_id is not null
end
return
end
go--调用函数实现查询
select a.*
from tb a,f_pid(5) b
where a.id=b.id
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert parent_id,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1
and a.parent_id is not null
end
return
end
go--调用函数实现查询
select a.*
from tb a,f_pid(5) b
where a.id=b.id
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select parent_id,@l --这里原来写错了
from tb a,@re b
where a.id=b.id and b.level=@l-1
and a.parent_id is not null
end
return
end
go--调用函数实现查询
select a.*
from tb a,f_pid(5) b
where a.id=b.id
go
create table tb(id int,parent_id int)
insert tb select 1,null
union all select 2,1
union all select 3,1
union all select 4,3
union all select 5,4
go--创建一个查询函数
create function f_pid(
@id int
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select parent_id,@l
from tb a,@re b
where a.id=b.id and b.level=@l-1
and a.parent_id is not null
end
return
end
go--调用函数实现查询
select a.*
from tb a,f_pid(5) b
where a.id=b.id
go--删除测试
drop table tb
drop function f_pid/*--测试结果id parent_id
----------- -----------
1 NULL
3 1
4 3
5 4(所影响的行数为 4 行)
--*/
declare @str varchar(100)set @str=''
select @pid= parent_id from 表 where id=5 --if 你要查找的id=5
while @pid is not null
begin
set @str=@str+cast(@pid as varchar(2))+','
select @pid=parent_id from 表 where id=@pid
endselect @str
declare @str varchar(100)set @str='5'--if 你要查找的id=5
select @pid= parent_id from 表 where id=cast(@str as int) while @pid is not null
begin
set @str=@str+','+cast(@pid as varchar(2))
select @pid=parent_id from 表 where id=@pid
endselect @str