--示例数据
create table aaa(id int,name varchar(10),parentid int)
insert aaa select 1,'a',null
union all select 2,'b',1
union all select 3,'c',2
union all select 4,'d',2
go
--查询函数
create function f_qry(
@str varchar(800)
)returns int
as
begin
declare @t table(id int,level int,sid varchar(8000))
declare @l int,@s varchar(8000)
select @s=@str,@l=0
insert @t select id,@l,name
from aaa
where parentid is null
and @str+'/' like name+'/%'
while @@rowcount>0 and @s<>''
begin
select @s=stuff(@s,1,charindex('/',@s+'/'),''),@l=@l+1
insert @t select a.id,@l,b.sid+'/'+a.name
from aaa a,@t b
where a.parentid=b.id and b.level=@l-1
and @s+'/' like a.name+'/%'
end
return((select id from @t where sid=@str))
end
go
--查询函数
create function f_qry(
@str varchar(800)
)returns int
as
begin
declare @id int
select @id=id,@str=stuff(@str,1,charindex('/',@str+'/'),'')
from aaa
where parentid is null --加多一个条件,保证是从最顶一级开始查询就行了
and @str+'/' like name+'/%'
while @@rowcount>0 and @str<>''
select @id=id,@str=stuff(@str,1,charindex('/',@str+'/'),'')
from aaa
where parentid=@id and @str+'/' like name+'/%'
return(@id)
end
但还差一步
由于name 字段不唯一
可能你的第一步就出现问题了
select @id=id,@str=stuff(@str,1,charindex('/',@str+'/'),'')
from aaa
where @str+'/' like name+'/%'
搜寻出来好多个结果只要了其中的一个
例如
字段: id, name, parentid
数值: 1 a <null>
2 b 1
3 c 2
4 d 2
5 f 1
6 a 5
--示例数据
create table aaa(id int,name varchar(10),parentid int)
insert aaa select 1,'a',null
union all select 2,'b',1
union all select 3,'c',2
union all select 4,'d',2
go
--查询函数
create function f_qry(
@str varchar(800)
)returns int
as
begin
declare @id int
select @id=id,@str=stuff(@str,1,charindex('/',@str+'/'),'')
from aaa
where @str+'/' like name+'/%'
while @@rowcount>0 and @str<>''
select @id=id,@str=stuff(@str,1,charindex('/',@str+'/'),'')
from aaa
where parentid=@id and @str+'/' like name+'/%'
return(@id)
end
go