基于一组命名的属性/值进行查询 [英] Querying based on a set of Named Attributes/Values

问题描述

我正在使用一组本质的属性/值对（实际上有更多的东西，但我为了这个问题简化）。有效地，您可以这样想到表：


实体（EntityID，AttributeName，AttributeValue）PK = EntityID，AttributeName

目标（TargetID，AttributeName，AttributeValue）PK = TargetID，AttributeName

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查询EntityID，TargetID的集合，Entity具有目标的所有属性以及相应的值？

How would you query with SQL the set of EntityID,TargetID for which an Entity has all the attributes for a target as well as the corresponding value?

EDIT（请求时为DDL）：

EDIT (DDL as requested):

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)
);

推荐答案

好，我经过多次尝试和编辑解决方案最终工作原理：

Okay, I think after several tries and edits, this solution finally works:

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 | 
+----------+----------+

---

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

---

To respond to your comment, here is a query with the tables reversed:

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);

这里是输出，上面输入数据相同。

And here's the output, given the same input data above.

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

这篇关于基于一组命名的属性/值进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！