我想这会满足你的要求。我使用LEFT OUTER JOIN s连接您正在搜索的每个参数。然后在顶部使用SELECT DISTINCT获取ItemId。

DECLARE @SearchColor nvarchar(50) = 'Red';
DECLARE @SearchLength nvarchar(50) = '100';

SELECT DISTINCT i.ItemId
FROM Info as i
    LEFT OUTER JOIN Info as iColor
        ON iColor.ItemId = i.ItemId 
        AND iColor.FieldName = 'Colour'
        AND iColor.Value = @SearchColor
    LEFT OUTER JOIN Info as iLength
        ON iLength.ItemId = i.ItemId 
        AND iLength.FieldName = 'Length'
        AND iLength.Value = @SearchLength
WHERE iColor.Id IS NOT NULL
    AND iLength.Id IS NOT NULL
;

这是一个经典的“剩余”问题

有很多解决方案，但如果你想使其灵活并能够处理不同数量的属性，那么你需要规范化你的输入。您可以使用表变量、临时表或TVP进行此操作。
一个常见且性能良好的解决方案是连接输入，将其分组并检查是否有足够的行

DECLARE @input TABLE (FieldName nvarchar(100), Value nvarchar(100), PRIMARY KEY (FieldName, Value));
INSERT @input VALUES
('Colour', 'Red'),
('Length', '100');

SELECT i.ItemId 
FROM Info i
JOIN @input inp
  ON inp.FieldName = i.FieldName
 AND inp.Value = i.Value
GROUP BY
  i.ItemId
HAVING COUNT(*) = (SELECT COUNT(*) FROM @input);

这里有一种方法可以在一个查询中获得两个结果并保留所有信息。

select   Id
        ,ItemId 
        ,FieldName  
        ,Value
from     (
          select   *
                  ,count(case when value = 'Red' then 1 when try_convert(int, value) = 100  then 1 end) over(partition by ItemId)                            as mrk 
                  ,count(case when value = 'Red' then 1 when try_convert(int, value) = 45 then 1 when value = 'Square' then 1 end) over(partition by ItemId) as mrk2 
          from     info
         ) info
where    mrk  = 2 
   or    mrk2 = 3

Fiddle升

第一个例子：

select    ItemId
from     (
          select   ItemId
                  ,case when value = 'Red' then 1 end                   as mrk
                  ,case when try_convert(int, value) = 100  then 1 end  as mrk2
          from     info
         ) info
group by  ItemId
having    max(mrk)  = 1
   and    max(mrk2) = 1

第二个例子：

select    ItemId
from     (
          select   ItemId
                  ,case when value = 'Red' then 1 end                  as mrk
                  ,case when try_convert(int, value) = 45  then 1 end  as mrk2
                  ,case when value = 'Square' then 1 end               as mrk3
          from     info
         ) info
group by  ItemId
having    max(mrk)  = 1
   and    max(mrk2) = 1
   and    max(mrk3) = 1

Fiddle升

我们可以旋转并简单地检索信息。

select *
from
(
select ItemId, FieldName, Value
from   info
) info
pivot(max(Value) for FieldName in(Colour, Length, Shape)) p
where Colour = 'red' and Length = '100'
   or Colour = 'red' and Length = '45' and shape = 'square'

Fiddle升

试试这个：

SELECT ItemId 
FROM Info 
WHERE (FieldName = 'Colour' AND Value = 'Red')
AND (FieldName = 'Length' AND Value = 100)