create table tabname(字段A varchar(10),字段B varchar(10)) insert into tabname select 'a','b' insert into tabname select 'b','c' insert into tabname select 'c','d' insert into tabname select 'd','e' create function get_root(@p varchar(10)) returns varchar(10) as begin while exists(select 1 from tabname where 字段B = @p) select @p = 字段A from tabname where 字段B = @p
return @p endselect dbo.get_root(字段B) from tabname
红尘够快,但是查询语句要改下select * from tabname Where 字段A=dbo.get_root('e')
没有其他的方法吗?比如象在oracle中 select * from tabname start with b='m90' connect by a=prior b就能将b='m90'下面的几个子级以及子级的子级的纪录都能取出来
insert into tabname select 'a','b'
insert into tabname select 'b','c'
insert into tabname select 'c','d'
insert into tabname select 'd','e'
create function get_root(@p varchar(10))
returns varchar(10)
as
begin
while exists(select 1 from tabname where 字段B = @p)
select @p = 字段A from tabname where 字段B = @p
return @p
endselect dbo.get_root(字段B) from tabname
select * from tabname start with b='m90' connect by a=prior b就能将b='m90'下面的几个子级以及子级的子级的纪录都能取出来