使用检索与查询多行AND和OR(Retrieve multiple rows with query

我想检索使用相同ID的多个行。因此，具有这种表“component_property”，我想有作为结果2条记录，ID的：8和9，根据我的SQL查询（请查看下面），但当然和检索什么的，因为我正在检查是否cp.property_id = 9102后来和检查是否cp.property_id = 8801 ，其在同一时间是不可能的。

ID;type_id;name;desc;property_id,value
--------------------------------------
8;3832;"amplifier1";"";8801;"3"
8;3832;"amplifier1";"";9102;"4015"
9;3832;"amplifier2";"";8801;"3"
9;3832;"amplifier2";"";9102;"4016"

这是查询我在这一刻它检索什么。

SELECT c.id, c.type_id, cp.property_id, cp.value 
FROM components_component AS c 
INNER JOIN components_componentproperty AS cp 
ON c.id = cp.component_id 
WHERE 
(cp.property_id = 9102  AND cp.value IN ('4015', '4016')) 
OR
(cp.property_id = 8801  AND cp.value = '3') 
AND c.type_id = 3832

部件===> component_property <===属性

零件

id serial NOT NULL,
type_id integer NOT NULL,
name character varying(50) NOT NULL,
description character varying(255),

component_property

id serial NOT NULL,
component_id integer NOT NULL,
property_id integer NOT NULL,
value character varying(255),

属性

id serial NOT NULL,
code character varying(10),
preferred_name character varying(50),

我预期的结果将是：

id;name
-------
8;amplifier1
9;amplifier2

这是关系分裂的情况下：

SELECT c.id, c.name
FROM   components_componentproperty cp1
JOIN   components_componentproperty cp2 USING (component_id)
JOIN   components_component         c   ON c.id = cp1.component_id
WHERE  cp1.property_id = 9102  AND cp1.value IN ('4015', '4016')
AND    cp2.property_id = 8801  AND cp2.value = '3'
AND    c.type_id = 3832
GROUP  BY c.id;

我们汇集在这里相关技术的阿森纳：

如何筛选SQL导致了一对多，通过关系

检查是否有大量的属性

您可以展开上面的查询和满手的属性将是最快的可能的解决方案之一。对于一个比较大的数字会更加方便（也开始要快）走这条路线：

5性质实施例，扩大视需要：

SELECT c.id, c.name
FROM  (
   SELECT id
   FROM  (
      SELECT component_id AS id, property_id  -- alias id just to shorten syntax
      FROM   components_componentproperty
      WHERE  property_id IN (9102, 8801, 1234, 5678, 9876)  -- expand as needed
      GROUP  BY 1,2
      ) cp1
   GROUP  BY 1
   HAVING count(*) = 5  -- match IN expression
   ) cp2
JOIN   components_component c USING (id);

内部子查询的额外步骤cp1仅仅是必要的，因为你明明有每多个条目(component_id, property_id)在components_componentproperty 。我们可以折叠cp1和cp2成一个和检查