我想使用相同的ID检索多行。因此,具有该表“ component_property”,我希望根据我的SQL查询(结果如下)检查结果为2条记录,ID为:8和9,但是当然不检索任何内容,因为我正在检查是否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),
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;
我们在这里组装了一系列相关技术:
您可以扩展上面的查询,并获得完整的属性,这将是最快的解决方案之一。对于更大的数字,走这条路线会更方便(并且也开始更快):
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)
in中显然有多个条目components_componentproperty
。我们可以折叠cp1
并cp2
成一个,并检查
HAVING count(DISTINCT property_id) = 5
但是我希望这样做会更昂贵,因为每行count(DISTINCT col)
需要一种排序操作。
对于很长的列表,这IN
是一个不好的选择。考虑:
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句