表结构如下
class_id object_id property_id value
1001 1 1 aaa
1001 1 2 bbb
1001 1 3 ccc
1001 2 1 eee
1001 2 2 fff
1002 2 1 eee
1003 2 2 fff
1003 2 3 ggg
我需要一个多条件查询,比如查询出满足条件class_id=1001 and ((property_id=1 and value=aaa) and (property_id=2 and value=bbb)) 的object_id注:表示可以接着加任意条件
class_id object_id property_id value
1001 1 1 aaa
1001 1 2 bbb
1001 1 3 ccc
1001 2 1 eee
1001 2 2 fff
1002 2 1 eee
1003 2 2 fff
1003 2 3 ggg
我需要一个多条件查询,比如查询出满足条件class_id=1001 and ((property_id=1 and value=aaa) and (property_id=2 and value=bbb)) 的object_id注:表示可以接着加任意条件
from 表结构如下 t
where class_id=1001=1001 and property_id=1 and value='aaa'
And exists (select 1 from 表结构如下 where class_id=1001=1001 and property_id=2 and value='bbb')
and exists (select 1 from 表结构如下 where class_id=1001=1001 And property_id=3 and value='ccc')
的结果都出来了
应该是or吧
from 表结构如下 t
where class_id=1001=1001 and property_id=1 and value='aaa'
And exists (select 1 from 表结构如下 where object_id=t.object_id and class_id=1001=1001 and property_id=2 and value='bbb')
and exists (select 1 from 表结构如下 where object_id=t.object_id and class_id=1001=1001 And property_id=3 and value='ccc')
SELECT a.* from tz a
left join ttz b on (b.object_id=a.object_id and b.class_id=1001 and b.property_id=2 and b.value='bbb')
left join ttz c on (c.object_id=a.object_id and c.class_id=1001 and c.property_id=3 and c.value='ccc')
where a.class_id=1001 and a.property_id=1 and a.value='aaa' and c.object_id is not null and b.object_id is not null
SELECT a.* from tTz a
INNER join ttz b on (b.object_id=a.object_id and b.class_id=1001 and b.property_id=2 and b.value='bbb')
INNER join ttz c on (c.object_id=a.object_id and c.class_id=1001 and c.property_id=3 and c.value='ccc')
where a.class_id=1001 and a.property_id=1 and a.value='aaa'
比如这两个条件是OR的
left join ttz b on (b.object_id=a.object_id and b.class_id=1001 and b.property_id=2 and b.value='bbb')
left join ttz c on (c.object_id=a.object_id and c.class_id=1001 and c.property_id=3 and c.value='ccc')
where a.class_id=1001 and a.property_id=1 and a.value='aaa'
and (c.object_id is not null or b.object_id is not null)假设只要满足1个条件即可
INNER join v_object b on (b.object_id=a.object_id and b.class_id=1001 and b.property_id=1 and b.value='张三')
INNER join v_object c on (c.object_id=a.object_id and c.class_id=1001 and c.property_id=2 and c.value='男')
where a.class_id=1001 and ( c.object_id is not null or b.object_id is not null)试了一下,并不能查询出满足property_id=1 and b.value='张三' 或property_id=2 and c.value='男'的记录