使用AND和OR通过查询检索多行 [英] Retrieve multiple rows with query using AND and OR

问题描述

我想使用相同的ID检索多行。因此，具有此表 component_property，我希望根据我的SQL查询获得结果2个记录，id为：8和9（检查下面的内容），但是由于我正在检查是否 cp.property_id = 9102 以及更高版本，然后检查 cp.property_id = 8801 是否同时无法实现。

I want to retrieve multiple rows using same id's. Therefore having this table "component_property", I would like to have as results 2 records, id's: 8 and 9 according to my SQL query (check below), but of course and retrieving nothing since I'm checking if cp.property_id = 9102 and later and checking if cp.property_id = 8801 which at the same time is impossible.

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"

这是我目前没有任何查询的查询。

This is the query I have at this moment which retrieves nothing.

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 ===> component_property< ===属性

component ===> component_property <=== property

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

component_property

component_property

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

属性

property

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

我的预期结果是：

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

推荐答案

这是一种关系分裂的情况：

This is a case of relational division:

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;

我们在这里集合了一系列相关技术：

We have assembled an arsenal of relevant techniques here:

• 如何过滤具有多次关系的SQL结果

• How to filter SQL results in a has-many-through relation

您可以展开上述查询，并获得完整的属性，这将是最快的解决方案之一。对于更大的数字，走这条路线会更方便（并且开始变得更快）：

You can expand the above query and for a hand full of properties it will be among the fastest possible solutions. For a bigger number it will be more convenient (and also starting to be faster) to go this route:

5个属性的示例，根据需要扩展：

Example for 5 properties, expand as needed:

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 只是必要的，因为您显然在 components_componentproperty 中的每个（component_id，property_id）有多个条目。我们可以将 cp1 和 cp2 折叠成一个并检查

The extra step of the inner subquery cp1 is only necessary, because you obviously have multiple entries per (component_id, property_id) in components_componentproperty. We could fold cp1 and cp2 into one and check

HAVING count(DISTINCT property_id) = 5

但我希望这样做会更昂贵，因为 count（DISTINCT col）需要每行 一种排序操作。

But I expect that to be more expensive, since count(DISTINCT col) needs one sort operation per row.

对于很长的列表， IN 是一个不好的选择。考虑：

For very long lists IN is a bad choice. Consider:

• 优化Postgres查询IN较大

• Optimizing a Postgres query with a large IN

这篇关于使用AND和OR通过查询检索多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！