在SQL中匹配多个键值对

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

SELECt *FROM @Objects mWHERe 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)

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

-- SchemaDECLARE @Objects TABLE (Id int);DECLARE @metadata TABLE (Id int, [Key] char(1), Value char(2));DECLARE @data TABLE ([Key] char(1), Value char(1));-- DataINSERT 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 metadataINSERT INTO @data VALUES  ('a','p'),  ('b','q');