Question

I am working with a set of what is essentially Attribute/Value pairs (there s actually quite a bit more to this, but I m simplifying for the sake of this question). Effectively you can think of the tables as such:

Entities (EntityID,AttributeName,AttributeValue) PK=EntityID,AttributeName
Targets (TargetID,AttributeName,AttributeValue) PK=TargetID,AttributeName

你会用SQL查询哪些实体ID，目标ID这个设置，其中实体具有目标的所有属性以及相应的值？

编辑（按要求的DDL）：

CREATE TABLE Entities(
    EntityID INTEGER NOT NULL,
    AttributeName CHAR(50) NOT NULL,
    AttributeValue CHAR(50) NOT NULL,
    CONSTRAINT EntitiesPK PRIMARY KEY (EntityID,AttributeName)
);
CREATE TABLE Targets(
    TargetID INTEGER NOT NULL,
    AttributeName CHAR(50) NOT NULL,
    AttributeValue CHAR(50) NOT NULL,
    CONSTRAINT TargetsPK PRIMARY KEY (TargetID,AttributeName)
);

Answer 1

好的，经过多次尝试和编辑，这个解决方案最终有效了：

SELECT e1.EntityID, t1.TargetID
FROM Entities e1
  JOIN Entities e2 ON (e1.EntityID = e2.EntityID)
  CROSS JOIN Targets t1
  LEFT OUTER JOIN Targets t2 ON (t1.TargetID = t2.TargetID
    AND e2.AttributeName = t2.AttributeName
    AND e2.AttributeValue = t2.AttributeValue)
GROUP BY e1.EntityID, t1.TargetID
HAVING COUNT(e2.AttributeValue) = COUNT(t2.AttributeValue);

测试数据：

INSERT INTO Entities VALUES 
 -- exact same attributes, should match
 (1,  Foo1 ,  123 ),
 (1,  Bar1 ,  123 ),
 -- same attributes but different values, should not match
 (2,  Foo2 ,  456 ),
 (2,  Bar2 ,  456 ),
 -- more columns in Entities, should not match
 (3,  Foo3 ,  789 ),
 (3,  Bar3 ,  789 ),
 (3,  Baz3 ,  789 ),
 -- fewer columns in Entities, should match
 (4,  Foo4 ,  012 ),
 (4,  Bar4 ,  012 ),
 -- same as case 1, should match Target 1
 (5,  Foo1 ,  123 ),
 (5,  Bar1 ,  123 ),
 -- one attribute with different value, should not match
 (6,  A ,  one ),
 (6,  B ,  two );

INSERT INTO Targets VALUES 
 (1,  Foo1 ,  123 ),
 (1,  Bar1 ,  123 ),
 (2,  Foo2 ,  abc ),
 (2,  Bar2 ,  abc ),
 (3,  Foo3 ,  789 ),
 (3,  Bar3 ,  789 ),
 (4,  Foo4 ,  012 ),
 (4,  Bar4 ,  012 ),
 (4,  Baz4 ,  012 ),
 (6,  A ,  one ),
 (6,  B ,  twox );

测试结果：

+----------+----------+
| EntityID | TargetID |
+----------+----------+
|        1 |        1 | 
|        4 |        4 | 
|        5 |        1 | 
+----------+----------+

回应您的评论，这是一个表格颠倒的查询：

SELECT e1.EntityID, t1.TargetID
FROM Targets t1
  JOIN Targets t2 ON (t1.TargetID = t2.TargetID)
  CROSS JOIN Entities e1
  LEFT OUTER JOIN Entities e2 ON (e1.EntityID = e2.EntityID
    AND t2.AttributeName = e2.AttributeName
    AND t2.AttributeValue = e2.AttributeValue)
GROUP BY e1.EntityID, t1.TargetID
HAVING COUNT(e2.AttributeValue) = COUNT(t2.AttributeValue);

这是给定上述相同输入数据的输出。

+----------+----------+
| EntityID | TargetID |
+----------+----------+
|        1 |        1 |
|        3 |        3 |
|        5 |        1 |
+----------+----------+

Answer 2

我喜欢这些类型的问题，但我认为希望提问者至少提供表格的创建脚本甚至一些样本数据并不过分。

我喜欢听听谁同意，谁不同意。

Answer 3

SELECT  *
FROM    (
    SELECT  eo.total,
        (
        SELECT  COUNT(*)
        FROM    Entities e, Targets t
        WHERE   e.EntityID = eo.EntityID
            AND t.TargetID = e.EntityID
            AND t.AttributeName = e.AttributeName
            AND t.AttributeValue = e.AttributeValue
        ) AS equal
    FROM    (
        SELECT  e.EntityID, COUNT(*) as total
        FROM    Entities e
        GROUP BY
            e.EntityID
        ) eo
    )
WHERE   total = equal

Answer 4

select distinct entityid,targetid
from   entities ent
,      targets  tar
where  not exists  
       (  select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid
          minus
          select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid)
and    not exists  
       (  select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid
          minus 
          select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid)
order by entityid,targetid
/

编辑1：

如果目标表中存在没有与实体表匹配的行是可以接受的，则解决方案变得简单：

select distinct entityid,targetid
from   entities ent
,      targets  tar
where  not exists  
       (  select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid
          minus 
          select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid)
order by entityid,targetid
/

编辑2：

理解“OP”的确切要求并不容易。

这是一个新的选择语句。我希望他能测试所有我的选择语句，以理解它们之间的差异。希望他有好的测试用例，知道自己想要什么。

select distinct entityid,targetid
from   entities ent
,      targets  tar
where  not exists  
       (  select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid
          minus  
          select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid)
order by entityid,targetid
/

友情链接