多值属性的sql查询

Question

我有每个由guid表示的资源，它们有属性名称-值对。我想查询具有给定属性名值对的资源。

所以，假设这个表看起来像：

GUID                                      ATTR_SUBTYPE        ATTR_VAL 
63707829116544a38c5a508fcde031a4            location            US 
63707829116544a38c5a508fcde031a4            owner               himanshu       
44d5bf579d9f4b9a8c41429d08fc51de            password            welcome1 
44d5bf579d9f4b9a8c41429d08fc51de            host                retailHost       
c67d8f5d1a9b41428f029d55b79263e1            key                 random 
c67d8f5d1a9b41428f029d55b79263e1            role                admin 

我要把所有的资源都定位为美国和业主作为奥拉夫。

一个可能的查询是：

select guid from table where attr_subtype = 'location' and attr_value = ‘US' INTERSECT select guid from table where attr_subtype = 'owner' and attr_value = ‘himanshu';

查询中可以有任意数量的属性名值对，因此在查询中每对有一个额外的交集。我想知道我们是否可以构造一个更好的查询，因为交叉非常昂贵。

Answer 1

假设每个GUID没有重复的属性，那么不需要JOIN就可以达到预期的结果

SELECT "GUID" FROM T
WHERE ( "ATTR_SUBTYPE" = 'location' AND "ATTR_VAL" = 'US' )
OR    ( "ATTR_SUBTYPE" = 'owner' AND "ATTR_VAL" = 'himanshu' )
GROUP BY "GUID"
HAVING COUNT(*) = 2 -- <-- keep only GUID have *both* attributes

请参阅http://sqlfiddle.com/#!4/80900/2

Answer 2

将目标插入临时表，然后加入到其中。

select t.guid 
from table as t 
join temp
on t.attr_subtype = temp.attr_subtype
and t.attr_value = temp.attr_value 

Answer 3

一般来说，连接比这里的相交更好。它提供了一个机会，以获得第一次记录之前，几个完整的表扫描将完成。但是无论如何，您选择了一个缓慢的数据结构，这样如果它减速，它就不会很好了。

试着做些像

select * 
from 
  (select * from table where attr_subtype = 'location' and attr_value = 'US') t1 
    join
  (select * from table where attr_subtype = 'owner' and attr_value = 'himanshu') t2
    on (t1.guid = t2.guid)
  ...