select distinct propertyGroupNo from 表 where attrId in ('0001','0002')
select PropertyGroupNo from 表 a inner join (select '0001' attrid ,'aaa' attrValue union all select '0002' attrid ,'bbb' attrValue) b on a.attrid=b.attrid and a.attrvalue=b.attrvalue
select Max(propertyGroupNo) as propertyGroupNo from 表 where (attrId+attrValue='0001aaa') or (attrId+attrValue)='0002bbb' Group by propertyGroupNo
select propertyGroupNo from 表 where (attrId= '0001' and attrValue ='aaa') and propertyGroupNo in (select propertyGroupNo from 表 where attrId= '0002' and attrValue ='bbb')
楼上要的是AND的条件,上面的语句是OR的条件. select PropertyGroupNo from 表 a where exists(select 1 from 表 b where a.PropertyGroupNo =b.PropertyGroupNo and b.attrid='0001' and attrVralue='aaa') and exists(select 1 from 表 b where a.PropertyGroupNo =b.PropertyGroupNo and b.attrid='0002' and attrVralue='bbb')
SQL> select * from test;PROPERTYGROUPNO ATTRID ATTRVALUE --------------- ---------- -------------------- 1001 1 aaa 1001 2 bbb 1002 1 aaa 1002 3 ccc 1003 4 ddd 1003 3 ccc已选择6行。SQL> select propertyGroupNo from test where attrId=0001 and attrValue='aaa' or(attrId=0002 and attrV alue='ddd');PROPERTYGROUPNO --------------- 1001 1002SQL> select propertyGroupNo from (select * from test where propertyGroupNo in (select propertyGroupN o from test where attrId=0001 and attrValue='aaa')) where attrId=0002 and attrValue='bbb';PROPERTYGROUPNO --------------- 1001SQL>
对啊,1002符合条件当然会查出来了!!! SQL> select propertyGroupNo from (select * from test where propertyGroupNo in 2 (select propertyGroupNo from test where attrId=0001 and attrValue='aaa')) 3 where attrId=0002 and attrValue='bbb';PROPERTYGROUPNO --------------- 1001 1002SQL>
表 a inner join
(select '0001' attrid ,'aaa' attrValue
union all
select '0002' attrid ,'bbb' attrValue) b
on a.attrid=b.attrid and a.attrvalue=b.attrvalue
from 表 where (attrId+attrValue='0001aaa') or (attrId+attrValue)='0002bbb'
Group by propertyGroupNo
attrId,attrValue 呵呵,这样的组合按楼主要的条件CRAZYFOR写的没问题
select PropertyGroupNo from
表 a
where
exists(select 1 from 表 b where a.PropertyGroupNo =b.PropertyGroupNo and b.attrid='0001' and attrVralue='aaa')
and
exists(select 1 from 表 b where a.PropertyGroupNo =b.PropertyGroupNo and b.attrid='0002' and attrVralue='bbb')
--------------- ---------- --------------------
1001 1 aaa
1001 2 bbb
1002 1 aaa
1002 3 ccc
1003 4 ddd
1003 3 ccc已选择6行。SQL> select propertyGroupNo from test where attrId=0001 and attrValue='aaa' or(attrId=0002 and attrV
alue='ddd');PROPERTYGROUPNO
---------------
1001
1002SQL> select propertyGroupNo from (select * from test where propertyGroupNo in (select propertyGroupN
o from test where attrId=0001 and attrValue='aaa')) where attrId=0002 and attrValue='bbb';PROPERTYGROUPNO
---------------
1001SQL>
1001 0001 aaa
1001 0002 bbb
1002 0001 aaa
1002 0002 bbb
1002 0003 ccc
1003 0004 ddd
1003 0002 bbb
1003 0003 ccc 如果是上面的记录,1002也会查出来的.
SQL> select propertyGroupNo from (select * from test where propertyGroupNo in
2 (select propertyGroupNo from test where attrId=0001 and attrValue='aaa'))
3 where attrId=0002 and attrValue='bbb';PROPERTYGROUPNO
---------------
1001
1002SQL>
1001 0001 aaa
1001 0002 bbb
1002 0001 aaa
1002 0002 bbb
1002 0003 ccc
1003 0004 ddd
1003 0002 bbb
1003 0003 ccc 对于这个源结果集,你要什么样的目标结果集?