在SQL中匹配多个键/值对

admin

在SQL中匹配多个键/值对

sql

我将元数据存储在SQL
Server的键/值表中。（我知道键/值不好，但这是用户提供的自由格式的元数据，因此我无法将键转换为列。）用户需要能够给我任意一组键/值对，并具有我返回所有符合所有这些条件的数据库对象。

例如：

Metadata:
Id Key Value
1  a   p
1  b   q
1  c   r
2  a   p
2  b   p
3  c   r

如果用户说a = p和b = q，我应该返回对象1（不是对象2，尽管它也有a = p，因为它有b = p。）

要匹配的元数据在具有简单键/值架构的表值sproc参数中。我最接近的是：

select * from [Objects] as o
where not exists (
  select * from [Metadata] as m
  join @data as n on (n.[Key] = m.[Key])
  and n.[Value] != m.[Value]
  and m.[Id] = o.[Id]
)

我的“不存在不匹配的行”是尝试通过形成其相反的方式来实现“所有行匹配”。这确实消除了元数据不匹配的对象，但它也返回了根本没有元数据的对象，因此效果不佳。

谁能指出我正确的方向？（性能和正确性的加分点。）

阅读 261

2021-07-01

共1个答案

admin

以下SQL查询产生所需的结果。

SELECT *
FROM @Objects m
WHERE Id IN
(
    -- Include objects that match the conditions:
    SELECT m.Id
    FROM @Metadata m
    JOIN @data d ON m.[Key] = d.[Key] AND m.Value = d.Value

    -- And discount those where there is other metadata not matching the conditions:
    EXCEPT
    SELECT m.Id
    FROM @Metadata m
    JOIN @data d ON m.[Key] = d.[Key] AND m.Value <> d.Value
)

测试我使用的架构和数据：

-- Schema
DECLARE @Objects TABLE (Id int);
DECLARE @Metadata TABLE (Id int, [Key] char(1), Value char(2));
DECLARE @data TABLE ([Key] char(1), Value char(1));

-- Data
INSERT INTO @Metadata VALUES
  (1,  'a',   'p'),
  (1,  'b',   'q'),
  (1,  'c',   'r'),
  (2,  'a',   'p'),
  (2,  'b',   'p'),
  (3,  'c',   'r');

INSERT INTO @Objects VALUES
  (1),
  (2),
  (3),
  (4);  -- Object with no metadata

INSERT INTO @data VALUES
  ('a','p'),
  ('b','q');

2021-07-01