select a.* from t1 a,t2 b where a.parent_id=b.id and a.name='大红'
--如果有多层,则写函数: create function f_cid( @name varchar(100) )returns @re table(id int,[level] int) as begin declare @l int,@id int select @id=id from t1 where name=@name set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @l=@l+1 insert @re select a.id,@l from t1 a,@re b where a.parent_id=b.id and b.[level]=@l-1 end return end go--调用实现查询 select a.* from t1 a,f_cid('大红') b where a.id=b.id
where a.parent_id=b.id
and a.name='大红'
create function f_cid(
@name varchar(100)
)returns @re table(id int,[level] int)
as
begin
declare @l int,@id int
select @id=id from t1 where name=@name
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l
from t1 a,@re b
where a.parent_id=b.id and b.[level]=@l-1
end
return
end
go--调用实现查询
select a.* from t1 a,f_cid('大红') b
where a.id=b.id