select * from table1 where (aname in (select distinct bname from table1)) or (bname in (select distinct aname from table1))
select * from t a where aname in(select bname from t where bname <>'') or bname in(select bname from t where bname <>'')
lsxaa(小李铅笔刀)你这样写满足条件吗??如果是 ID AName BName 1 a 2 b a 3 c b 4 d c 5 e f 6 f d 或者 ID AName BName 1 a 2 b a 3 c b 4 d c 5 e 6 f d 7 g e 总觉得楼主的意思不是很明确!
这样就行了select * from t a where aname in(select bname from t where bname <>'') or exists(select 1 from t where t.aname=a.bname)
lsxaa(小李铅笔刀)你没有明白我的意思! ID AName BName 1 a 2 b a 3 c b 4 d c 5 e 6 f d 7 g e 象这种,6,7算是关联吗?? 或者 ID AName BName 1 a 2 b a 3 c b 4 d c 5 e 6 f g 7 g f
ID AName BName 1 a 2 b a 3 c b 4 d c 5 e 6 f g 7 g fselect * from t a where aname in(select bname from t where bname <>'') or exists(select 1 from t where t.aname=a.bname)--结果 ID AName BName ----------- ----- ----- 1 a 2 b a 3 c b 4 d c 6 f g 7 g f(所影响的行数为 6 行)
不是 ID AName BName 1 a 2 b a 3 c b 4 d c 5 e 6 f d 7 g e 8 i 9 h 如果是这样的话,我需要选出和a关联的那么应该能选出1,2,3,4,6这几条记录,关联的条件还要判断和那个Aname关联,同样,要选出和b,c,d,f关联的也是这几个,但是要选出和e或者g关联的只有5,7这几条记录,可能还有一些是没有任何关联的,如8和9这两条记录
所以我说嘛!!楼主根本就没有把条件讲清楚!害的我和lsxaa(小李铅笔刀) 吵半天
select id,Aname ,Bname from 表 where Aname in (select Aname as name from 表 union select Bname as name from 表 ) or Bname in (select Aname as name from 表 union select Bname as name from 表 )
davorsuker39(大狐狸) 我只是猜的...不和你争了 看楼主怎么说吧.... 呵呵呵
写错了 select id,Aname ,Bname from 表 where Aname in (select Bname from 表 group Bname) or Bname is not Null
from table1
where (aname in (select distinct bname from table1))
or (bname in (select distinct aname from table1))
where aname in(select bname from t where bname <>'')
or bname in(select bname from t where bname <>'')
ID AName BName
1 a
2 b a
3 c b
4 d c
5 e f
6 f d
或者
ID AName BName
1 a
2 b a
3 c b
4 d c
5 e
6 f d
7 g e
总觉得楼主的意思不是很明确!
where aname in(select bname from t where bname <>'')
or exists(select 1 from t where t.aname=a.bname)
ID AName BName
1 a
2 b a
3 c b
4 d c
5 e
6 f d
7 g e
象这种,6,7算是关联吗??
或者
ID AName BName
1 a
2 b a
3 c b
4 d c
5 e
6 f g
7 g f
1 a
2 b a
3 c b
4 d c
5 e
6 f g
7 g fselect * from t a
where aname in(select bname from t where bname <>'')
or exists(select 1 from t where t.aname=a.bname)--结果
ID AName BName
----------- ----- -----
1 a
2 b a
3 c b
4 d c
6 f g
7 g f(所影响的行数为 6 行)
ID AName BName
1 a
2 b a
3 c b
4 d c
5 e
6 f d
7 g e
8 i
9 h
如果是这样的话,我需要选出和a关联的那么应该能选出1,2,3,4,6这几条记录,关联的条件还要判断和那个Aname关联,同样,要选出和b,c,d,f关联的也是这几个,但是要选出和e或者g关联的只有5,7这几条记录,可能还有一些是没有任何关联的,如8和9这两条记录
from 表
where Aname in (select Aname as name from 表
union
select Bname as name from 表
)
or Bname in (select Aname as name from 表
union
select Bname as name from 表
)
select id,Aname ,Bname
from 表
where Aname in (select Bname from 表 group Bname)
or Bname is not Null