由表如下：
col1 col2
----------------------------------
A a
A b
B b
C a
C c
D e
E g
问题描述：
给定一个col2的值（比如a），要求取出所有与a直接或间接发生关系的数据，
其结果应为两组数值：
A;B;C a;b;c
(注：表中数据较大，至少5k以上，如何获得最佳的执行效率)
请各位大虾踊跃发言，谢谢！！！
------解决方案--------------------
第一组：
select distinct(id) from tmp3 where name in (
select name from tmp3 where id in
(select id from tmp3 where name= 'a ')
)
第二组：
select distinct(name) from tmp3 where id in
(select id from tmp3 where name= 'a ')
其中id即col1,name即col2
------解决方案--------------------
/*
问题描述：
给定一个col2的值（比如a），要求取出所有与a直接或间接发生关系的数据，
其结果应为两组数值：
A;B;C a;b;c*/
create table tb(col1 char,col2 char)
insert into tb
select 'A ', 'a ' union all
select 'A ', 'b ' union all
select 'B ', 'b ' union all
select 'C ', 'a ' union all
select 'C ', 'c ' union all
select 'D ', 'e ' union all
select 'E ', 'g '
go
create proc p(@col2 char)
as
declare @i int,@s1 varchar(100),@s2 varchar(400)
set @s1= ' '
set @s2= ' '
set @i=0
declare @tb table (col1 char,col2 char,level int)
insert into @tb
select col1,col2,@i from tb where [email protected]
while @@rowcount> 0
begin
set @[email protected]+1
insert into @tb
select a.col1,a.col2,@i from tb a,@tb b
where (a.col1=b.col1 or a.col2=b.col2) and [email protected]
and ( not exists(select 1 from @tb where col1=a.col1 )
or not exists(select 1 from @tb where col2=a.col2 ) )
end
select @[email protected]+ '; '+col1 from (select distinct col1 from @tb) a
select @[email protected]+ '; '+col2 from (select distinct col2 from @tb) a
select stuff(@s1,1,1, ' '),stuff(@s2,1,1, ' ')
go
p 'a '
drop table tb
drop proc p