oracle 查询SQL求助(递归、死循环)

Question

   --groupid:组编号;studentname:组成员
   create table tgroup(groupid varchar2(20),studentname varchar2(50));
   insert into tgroup('1','张三');
   insert into tgroup('1','李四');
   insert into tgroup('1','王五');   insert into tgroup('2','张三');
   insert into tgroup('2','刘柳');
   
   insert into tgroup('3','王五');
   insert into tgroup('3','沈七');
我想查询跟张三有关系(直接关系和间接关系)的所有成员，请问该SQL如何写？
如上面的数据，张三和李四、王五在一组，同时王五又和沈七在一组，那么沈七和张三就是间接关系
请问如何写这个SQL. 

Answer 1

select * from tgroup a where exist(select 1 from tgroup b where b.studentname ='张三' and b.groupid =a.groupid )

Answer 2

SQL> create table tgroup(groupid varchar2(20),studentname varchar2(50));表已创建。SQL> insert into tgroup values ('1','张三');已创建 1 行。SQL> insert into tgroup values ('1','李四');已创建 1 行。SQL> insert into tgroup values ('1','王五');已创建 1 行。SQL> 
SQL> insert into tgroup values ('2','张三');已创建 1 行。SQL> insert into tgroup values ('2','刘柳');已创建 1 行。SQL> 
SQL> insert into tgroup values ('3','王五');已创建 1 行。SQL> insert into tgroup values ('3','沈七');已创建 1 行。SQL> 
SQL> insert into tgroup values ('4','陈八');已创建 1 行。SQL> insert into tgroup values ('4','章九');已创建 1 行。SQL> --测试一
SQL> with t(groupid,studentname,lvl) as(
  2  select groupid,studentname,0 from tgroup where studentname='张三'
  3  union all
  4  select tgroup.groupid,tgroup.studentname,lvl+1 from tgroup,t
  5  where (tgroup.studentname<>t.studentname and tgroup.groupid=t.groupid
  6   and mod(lvl,2)=0) or(
  7   tgroup.studentname=t.studentname and tgroup.groupid<>t.groupid
  8   and mod(lvl,2)=1
  9   )
 10  )
 11  select distinct studentname from t;STUDENTNAME                                                                     
--------------------------------------------------------------------------------
张三                                                                            
王五                                                                            
刘柳                                                                            
沈七                                                                            
李四                                                                            SQL> --测试二
SQL> with t(groupid,studentname,lvl) as(
  2  select groupid,studentname,0 from tgroup where studentname='陈八'
  3  union all
  4  select tgroup.groupid,tgroup.studentname,lvl+1 from tgroup,t
  5  where (tgroup.studentname<>t.studentname and tgroup.groupid=t.groupid
  6   and mod(lvl,2)=0) or(
  7   tgroup.studentname=t.studentname and tgroup.groupid<>t.groupid
  8   and mod(lvl,2)=1
  9   )
 10  )
 11  select distinct studentname from t;STUDENTNAME                                                                     
--------------------------------------------------------------------------------
章九                                                                            
陈八      

Answer 3

SQL> create table tgroup(groupid varchar2(20),studentname varchar2(50));表已创建。SQL> insert into tgroup values ('1','张三');已创建 1 行。SQL> insert into tgroup values ('1','李四');已创建 1 行。SQL> insert into tgroup values ('1','王五');已创建 1 行。SQL> 
SQL> insert into tgroup values ('2','张三');已创建 1 行。SQL> insert into tgroup values ('2','刘柳');已创建 1 行。SQL> 
SQL> insert into tgroup values ('3','王五');已创建 1 行。SQL> insert into tgroup values ('3','沈七');已创建 1 行。SQL> 
SQL> insert into tgroup values ('4','陈八');已创建 1 行。SQL> insert into tgroup values ('4','章九');已创建 1 行。SQL> --测试一
SQL> with t(groupid,studentname,lvl) as(
  2  select groupid,studentname,0 from tgroup where studentname='张三'
  3  union all
  4  select tgroup.groupid,tgroup.studentname,lvl+1 from tgroup,t
  5  where (tgroup.studentname<>t.studentname and tgroup.groupid=t.groupid
  6      and mod(lvl,2)=0) or(
  7          tgroup.studentname=t.studentname and tgroup.groupid<>t.groupid
  8          and mod(lvl,2)=1
  9      )
 10  )
 11  select distinct studentname from t;STUDENTNAME                                                                     
--------------------------------------------------------------------------------
张三                                                                            
王五                                                                            
刘柳                                                                            
沈七                                                                            
李四                                                                            SQL> --测试二
SQL> with t(groupid,studentname,lvl) as(
  2  select groupid,studentname,0 from tgroup where studentname='陈八'
  3  union all
  4  select tgroup.groupid,tgroup.studentname,lvl+1 from tgroup,t
  5  where (tgroup.studentname<>t.studentname and tgroup.groupid=t.groupid
  6      and mod(lvl,2)=0) or(
  7          tgroup.studentname=t.studentname and tgroup.groupid<>t.groupid
  8          and mod(lvl,2)=1
  9      )
 10  )
 11  select distinct studentname from t;STUDENTNAME                                                                     
--------------------------------------------------------------------------------
章九                                                                            
陈八      

Answer 4

Select Distinct t.studentname
  From tgroup t
 Where connect_by_isleaf = 1
 Start With t.studentname = '张三'
Connect By nocycle Prior t.studentname != t.studentname
       And t.groupid = t.groupid
不知道还能满足?

Answer 5

drop table tgroup;
create table tgroup(groupid varchar2(20),studentname varchar2(50));
insert into tgroup values('1','张三');
insert into tgroup values('1','李四');
insert into tgroup values('1','王五');insert into tgroup values('2','张三');
insert into tgroup values('2','刘柳');
    
insert into tgroup values('3','王五');
insert into tgroup values('3','沈七');insert into tgroup values('4','赵六');
insert into tgroup values('4','沈七');insert into tgroup values('5','陈八');
insert into tgroup values('5','钱二');insert into tgroup values('5','钱二');
commit;
select distinct studentname from tgroup
where groupid in (select id 
from (select distinct a.groupid pid,
             b.groupid id
      from tgroup a,
           tgroup b
      where a.studentname = b.studentname
      and a.groupid < b.groupid )
start with pid = 1
connect by nocycle prior id = pid)
or groupid = 1

Answer 6

select distinct studentname from tgroup 
where exists(
select 1 from 
(select t.studentname n from 
(select groupid g from tgroup where studentname='沈七')a,tgroup t
where a.g=t.groupid
and t.studentname <> '沈七')s,tgroup p
where s.n=p.studentname)

Answer 7

验证发现 该SQL有问题；
insert into tgroup values ('1','张三');
  insert into tgroup values('1','李四');
  insert into tgroup values('1','王五');  insert into tgroup values('2','张三');
  insert into tgroup values('2','刘柳');
    
  insert into tgroup values('3','王五');
  insert into tgroup values('3','沈七');
  insert into tgroup values('3','张三');
  
  insert into tgroup values('4','沈七');
  insert into tgroup values('4','无名');
应该输出‘无名’，但SQL没有输出

Answer 8

select a.STUDENTNAME name from tgroup a where GROUPID in (select groupid from tgroup where STUDENTNAME='张三') 
UNION 
select a.STUDENTNAME name from tgroup a where GROUPID in (select groupid from tgroup where STUDENTNAME  in(select a.STUDENTNAME name from tgroup a where GROUPID in (select groupid from tgroup where STUDENTNAME='张三')
))

Answer 9

select a.STUDENTNAME 直接关系,'1' 间接关系  from tgroup a where GROUPID in (select groupid from tgroup where STUDENTNAME='张三') and a.STUDENTNAME!='张三'
UNION 
select '1' 直接关系,a.STUDENTNAME 间接关系  from tgroup a where GROUPID in (select groupid from tgroup where STUDENTNAME  in(select a.STUDENTNAME name from tgroup a where GROUPID in (select groupid from tgroup where STUDENTNAME='张三')
)) and a.STUDENTNAME!='张三'
这个就是没有去掉重复的 间接关系和直接关系

Answer 10

SQL> create table tgroup(groupid varchar2(20),studentname varchar2(50));表已创建。SQL> insert into tgroup values ('1','张三');已创建 1 行。SQL> insert into tgroup values ('1','李四');已创建 1 行。SQL> insert into tgroup values ('1','王五');已创建 1 行。SQL> 
SQL> insert into tgroup values ('2','张三');已创建 1 行。SQL> insert into tgroup values ('2','刘柳');已创建 1 行。SQL> 
SQL> insert into tgroup values ('3','王五');已创建 1 行。SQL> insert into tgroup values ('3','沈七');已创建 1 行。SQL> 
SQL> insert into tgroup values ('4','陈八');已创建 1 行。SQL> insert into tgroup values ('4','章九');已创建 1 行。SQL> --测试一
SQL> with t(groupid,studentname,lvl) as(
  2  select groupid,studentname,0 from tgroup where studentname='张三'
  3  union all
  4  select tgroup.groupid,tgroup.studentname,lvl+1 from tgroup,t
  5  where (tgroup.studentname<>t.studentname and tgroup.groupid=t.groupid
  6      and mod(lvl,2)=0) or(
  7          tgroup.studentname=t.studentname and tgroup.groupid<>t.groupid
  8          and mod(lvl,2)=1
  9      )
 10  )
 11  select distinct studentname from t;STUDENTNAME                                                                     
--------------------------------------------------------------------------------
张三                                                                            
王五                                                                            
刘柳                                                                            
沈七                                                                            
李四                                                                            SQL> --测试二
SQL> with t(groupid,studentname,lvl) as(
  2  select groupid,studentname,0 from tgroup where studentname='陈八'
  3  union all
  4  select tgroup.groupid,tgroup.studentname,lvl+1 from tgroup,t
  5  where (tgroup.studentname<>t.studentname and tgroup.groupid=t.groupid
  6      and mod(lvl,2)=0) or(
  7          tgroup.studentname=t.studentname and tgroup.groupid<>t.groupid
  8          and mod(lvl,2)=1
  9      )
 10  )
 11  select distinct studentname from t;STUDENTNAME                                                                     
--------------------------------------------------------------------------------
章九                                                                            
陈八      这个其实可以。不过只能在oracle11上才支持。
不支持该功能的oracle只有通过connect by来代替。
我写了个，你看看。希望对你有帮助
Select Distinct t.studentname
  From tgroup t
 Where studentname!= '张三'
 Start With t.studentname = '张三'
Connect By nocycle ((Prior t.studentname != t.studentname 
and Prior t.groupid = t.groupid) or
(Prior t.studentname = t.studentname 
and Prior t.groupid != t.groupid))