代码之家 › 专栏 › 技术社区 › Mark Roddy

基于一组命名属性/值进行查询

sql-match-all oracle database sql

Mark Roddy · 技术社区 · 17 年前

我正在处理一组本质上是属性/值对的内容(实际上还有很多内容,但为了这个问题,我正在简化这些内容)。实际上,您可以这样认为这些表:

实体(entityid,attributename,attributevalue)pk=entityid,attributename
目标(targetid,attributename,attributevalue)pk=targetid,attributename

您将如何使用SQL查询一个实体具有目标的所有属性以及相应值的EntityID和TargetId集?

编辑(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)
);

4 回复 | 直到 14 年前

Bill Karwin 17 年前

好吧,我想经过几次尝试和编辑,这个解决方案最终会起作用:

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

tuinstoel 17 年前

我喜欢这些问题,但我认为希望OP至少为表和一些示例数据提供创建脚本并不是不合理的。

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

Quassnoi 17 年前

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

tuinstoel 17 年前

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
/

Eddi1:

如果目标表中的行与实体表中的行不匹配,则解决方案简化为:

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语句。我希望他能测试我所有的选择语句来理解这些差异。我希望他有好的测试用例,知道他想要什么。

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
/