select distinct aa
from tablename t1
where bb='1'
and exists (
select * from tablename t2
where t2.aa=t1.aa
and t2.bb='2'
)
and exists (
select * from tablename t3
where t3.aa=t1.aa
and t3.bb='3'
)这不是什么难事,问题是你的例子错误,结果应该只有test1 。
from tablename t1
where bb='1'
and exists (
select * from tablename t2
where t2.aa=t1.aa
and t2.bb='2'
)
and exists (
select * from tablename t3
where t3.aa=t1.aa
and t3.bb='3'
)这不是什么难事,问题是你的例子错误,结果应该只有test1 。
from ...
where bb= '1'
这样会不会清楚一点:
select distinct aa from tb as bbb
where (exists (select * from tb as aaa where aaa.bb='1' and aaa.aa=bbb.aa)
and
exists (select * from tb as aaa where aaa.bb='2' and aaa.aa=bbb.aa)
and
exists (select * from tb as aaa where aaa.bb='3' and aaa.aa=bbb.aa)
)
你说的条件可以任意是什么意思?
from table1 as
group by aa
having bb='1' and bb='2' and bb='3' and (aa <>'test1' and aa <>'test3')
union all
select distinct aa
from table1 as
group by aa
having bb='1' or bb='2' or bb='3' and (aa in('test1','test3'))
from table1 as
group by aa
having bb='1' and bb='2' and bb='3'
Where aa <>'test1' and aa <>'test3'
union all
select distinct aa
from table1 as
group by aa
having bb='1' or bb='2' or bb='3'
Where aa in('test1','test3')
from (
select distinct aa,bb from table1
) as c
where bb='1' or bb='2' or bb='3'
group by aa
having count(*>1)
select aa from tablename group by aa
having sum((case bb when 1 then 1 when 2 then 1 when 3 then 1 else 0 end))=3
表结构修改如下较好:
字段 名 类型
aa varchar 可重复
bb1 varchar '0' 或 '1'
bb2 varchar '0' 或 '1'
bb3 varchar '0' 或 '1'
having sum((case bb when 1 then 1 when 2 then 1 when 3 then 1 else 0 end))>1
select aa
from (
select distinct aa,bb from table1
) as c
where bb='1' or bb='2' or bb='3'
group by aa
having count(*) >1
“bb字段值=1 and 2 and 3 的记录的aa字段的值(不能重复),”
后说:
“bb同时等于1,2,3的有test1,test3,条件可以任意组合”
还是不对,怎么会有test1,test3呢,怎么叫“条件可以任意组合”
看看你自己的数据,有test1,test3又怎么没有test2呢???浪费感情!!上面两个回复都是没有用的,根本不知道要什么!!