select distinct exg from [table] a,[table] b,[table] c where a.exg=b.exg and a.exg=c.exg and a.img='a' and b.img='b' and c.img='c'
--对不起,我一楼有点问题 --测试环境 create table T1 (exg varchar(20),img varchar(10)) insert T1 select 'A','a' union all select 'A','b' union all select 'A','c' union all select 'B','a' union all select 'B','b' union all select 'B','d' union all select 'C','b' union all select 'C','e' union all select 'C','c' union all select 'D','a' union all select 'D','e' union all select 'D','d' --建函数 create function FString(@exg as varchar(20)) returns varchar(200) as begin declare @s as varchar(100) set @s='' select @s=@S+','+ltrim(rtrim(img)) from ( select img from T1 where exg=@exg) A return stuff(@s,1,1,'') end--查询结果 declare @s as varchar(100) set @s='b,e,c' select distinct exg from T1 where dbo.FString(exg)='b,e,c'-查询结果 exg -------------------- C(所影响的行数为 1 行) --呵呵,看看有没有其它办法了!
create table T1 (exg varchar(20),img varchar(10)) go insert T1 select 'A','a' union all select 'A','b' union all select 'A','c' union all select 'B','a' union all select 'B','b' union all select 'B','d' union all select 'C','b' union all select 'C','e' union all select 'C','c' union all select 'D','a' union all select 'D','e' union all select 'D','d' goselect t1.exg from t1,(select t1.exg from t1 where img='b' ) b,(select t1.exg from t1 where img='e' ) c where img='c' and t1.exg=b.exg and t1.exg=c.exg --drop table t1
bugchen888(臭虫) 的方法好点-。-
--个人感觉用三个表连接的话,会比较慢 --测试数据 create table T1 (exg varchar(20),img varchar(10)) insert T1 select 'A','a' union all select 'A','b' union all select 'A','c' union all select 'B','a' union all select 'B','b' union all select 'B','d' union all select 'C','b' union all select 'C','e' union all select 'C','c' union all select 'D','a' union all select 'D','e' union all select 'D','d' --查询 select exg from (select distinct * from t1)t where img='b' or img='e' or img='c' group by exg having count(*)=3 --清除 drop table t1
where a.exg=b.exg
and a.exg=c.exg
and a.img='a'
and b.img='b'
and c.img='c'
--测试环境
create table T1 (exg varchar(20),img varchar(10))
insert T1 select 'A','a'
union all select 'A','b'
union all select 'A','c'
union all select 'B','a'
union all select 'B','b'
union all select 'B','d'
union all select 'C','b'
union all select 'C','e'
union all select 'C','c'
union all select 'D','a'
union all select 'D','e'
union all select 'D','d'
--建函数
create function FString(@exg as varchar(20))
returns varchar(200)
as
begin
declare @s as varchar(100)
set @s=''
select @s=@S+','+ltrim(rtrim(img)) from
( select img from T1 where exg=@exg)
A
return stuff(@s,1,1,'')
end--查询结果
declare @s as varchar(100)
set @s='b,e,c'
select distinct exg from T1 where dbo.FString(exg)='b,e,c'-查询结果
exg
--------------------
C(所影响的行数为 1 行)
--呵呵,看看有没有其它办法了!
go
insert T1 select 'A','a'
union all select 'A','b'
union all select 'A','c'
union all select 'B','a'
union all select 'B','b'
union all select 'B','d'
union all select 'C','b'
union all select 'C','e'
union all select 'C','c'
union all select 'D','a'
union all select 'D','e'
union all select 'D','d'
goselect t1.exg
from t1,(select t1.exg
from t1
where img='b' ) b,(select t1.exg
from t1
where img='e' ) c
where img='c' and t1.exg=b.exg and t1.exg=c.exg
--drop table t1
的方法好点-。-
--测试数据
create table T1 (exg varchar(20),img varchar(10))
insert T1 select 'A','a'
union all select 'A','b'
union all select 'A','c'
union all select 'B','a'
union all select 'B','b'
union all select 'B','d'
union all select 'C','b'
union all select 'C','e'
union all select 'C','c'
union all select 'D','a'
union all select 'D','e'
union all select 'D','d'
--查询
select exg
from (select distinct * from t1)t
where img='b' or img='e' or img='c'
group by exg
having count(*)=3
--清除
drop table t1