以下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');
