Oracle查询:一对多关系

Question

在我的应用程序中有一对多关系，如下所述。

表一：应用

app_id	app_name
1	ABC
2	XYZ

您想要选择存在特定属性的应用程序。因此，从应用程序表中进行选择，并使用where子句检查是否存在具有EXISTS或IN:的属性

select * 
from aplication
where app_id in (select app_id from application_attribute where attr_name = 'attr1' and attr_value = 'white')
and app_id in (select app_id from application_attribute where attr_name = 'attr2' and attr_value = '12')
and app_id in (select app_id from application_attribute where attr_name = 'attr3' and attr_value = '45')
order by app_id;

至于泛型：只需使用编程语言和所需属性的循环来构建查询。在Oracle中，您可以为此使用PL/SQL。

您可以在相关的子查询中使用EXISTS和HAVING子句：

SELECT *
FROM   application a
WHERE  EXISTS (
SELECT 1
FROM   application_attribute aa
WHERE  a.app_id = aa.app_id
AND    (aa.attr_name, aa.attr_value)
IN (('attr1', 'white'), ('attr2', '12'), ('attr3', '45'))
HAVING COUNT(/*DISTINCT*/ aa.attr_name) = 3 
)

注意：如果可以有重复的属性值，则可以使用COUNT(DISTINCT ...)而不仅仅是COUNT(...)

对于样本数据：

CREATE TABLE application (app_id, app_name) AS
SELECT 1, 'ABC' FROM DUAL UNION ALL
SELECT 2, 'XYZ' FROM DUAL;
CREATE TABLE Application_attribute (App_attr_id, app_id, attr_name, attr_value) AS
SELECT 1, 1, 'attr1', 'white' FROM DUAL UNION ALL
SELECT 2, 1, 'attr2', '12' FROM DUAL UNION ALL
SELECT 3, 1, 'attr3', '45' FROM DUAL UNION ALL
SELECT 4, 2, 'attr1', 'red' FROM DUAL UNION ALL
SELECT 5, 2, 'attr2', '12' FROM DUAL UNION ALL
SELECT 6, 2, 'attr4', '45' FROM DUAL UNION ALL
SELECT 7, 2, 'attr7', '62' FROM DUAL;

输出：

APP_ID	APP_NAME
1	ABC

您只能通过有条件地匹配attr_name和attr_value列的值来联接表一次，同时只筛选出具有三重属性名称(如)的返回记录的结果

SELECT a.app_id, a.app_name
FROM application a
JOIN application_attribute aa
ON aa.app_id = a.app_id
WHERE DECODE( aa.attr_name, 'attr1', aa.attr_value ) = 'white' -- you can make these literals parametric by prefixing with : or & such as &p_attr1 and enter 'white' whenever prompted
OR DECODE( aa.attr_name, 'attr2', aa.attr_value ) = '12'
OR DECODE( aa.attr_name, 'attr3', aa.attr_value ) = '45'  
GROUP BY a.app_id, a.app_name
HAVING COUNT(DISTINCT aa.attr_name)=3  

演示

如果我们使用联接重新编写查询，则需要在WHERE中更改3个变量。这将使修改它们变得更加容易。

Select a.* 
from application a
left join application-attribute at1 on a.app_id = at1.app_id and at1.attr_name = 'attr1'
left join application_attribute at2 on a.app_id = at2.app_id and at2.attr_name = 'attr2'
left join application_attribute at3 on a.app_id = at3.app_id and at3.attr_name = 'attr3'
left join application_attribute at4 on a.app_id = at4.app_id and at4.attr_name = 'attr4'
where  at1.attr_value = 'white' 
and at2.attr_value = '12' 
and at3.attr_value = '45'
/*   and at4.attr_value = not needed */
;