假设你的表叫yourtalbe, 字段类型为Varchar(10)
--
begin
--
create table #t1(Field varchar(10))
--
declare @C1 int
--
insert into #t select field1 from yourtalbe where field1='a' group by field1
--
select @c1=@@rowcount
--
while @C1>0
begin
--
insert into #t1
select field2 from yourtalbe a, #t1 b where a.field1=b.field group by field2
--
select @c1=@@rowcount
--
end
--
select field into #t2 from #t1 group by field
--
select a.* from yourtalbe a, #t2 b where a.field1=b.field
--
end
--
return
如果只存在两层关系的数据的话,可以用以下语句,urtable是表名;
select * from urtable where (field1 in (select field2 from urtable where field1='a')) or field1='a';
如果存在与a的关联关系达到两层以上的数据的话,我看你还是使用树的遍历吧.
create table #t1(Field varchar(10))
create table #t2(Field varchar(10))
declare @C1 int,@C2 int
select @C1=0
select @C2=1
while @C2>@C1
begin
select @C1=@C2
delete from #T2
insert into #T2(Field)
select distinct(Field2)
from yourtable, #T1
where yourtable.Field1=#T1.Field or yourtable.Field1='a'
delete from #T1
insert into #T1(Field)
select Field from #T2
select @C2=count(*) from #T2
end
select y.* from yourtable y ,#T1 where y.field1=#T1.Field