如何基于多条件筛选属性多行存储的SQL Server Info表ItemId

你手里的Info表属于**EAV（实体-属性-值）**结构，这种表把实体的属性拆成多行存储，直接单条件查询没法实现多属性的“且”逻辑，下面给你几种能满足需求的SQL写法：

明确需求 #

• 需求1：找出同时满足Colour='Red'和Length='100'的ItemId，预期结果：308
• 需求2：找出同时满足Colour='Red'、Length='45'、Shape='Square'的ItemId，预期结果：309

方法1：分组统计匹配数（最通用） #

思路是先筛选出所有符合任一条件的记录，再按ItemId分组，只保留满足所有条件的分组（统计匹配的不同属性数量等于条件数）。

需求1实现：

SELECT ItemId
FROM Info
WHERE (FieldName = 'Colour' AND Value = 'Red')
   OR (FieldName = 'Length' AND Value = '100')
GROUP BY ItemId
HAVING COUNT(DISTINCT FieldName) = 2;

需求2实现：

SELECT ItemId
FROM Info
WHERE (FieldName = 'Colour' AND Value = 'Red')
   OR (FieldName = 'Length' AND Value = '45')
   OR (FieldName = 'Shape' AND Value = 'Square')
GROUP BY ItemId
HAVING COUNT(DISTINCT FieldName) = 3;

方法2：自连接（直观易懂） #

把同一个表按不同属性条件多次关联，确保同一个ItemId同时满足所有条件。

需求1实现：

SELECT i1.ItemId
FROM Info i1
INNER JOIN Info i2 ON i1.ItemId = i2.ItemId
WHERE i1.FieldName = 'Colour' AND i1.Value = 'Red'
  AND i2.FieldName = 'Length' AND i2.Value = '100';

需求2实现：

SELECT i1.ItemId
FROM Info i1
INNER JOIN Info i2 ON i1.ItemId = i2.ItemId
INNER JOIN Info i3 ON i1.ItemId = i3.ItemId
WHERE i1.FieldName = 'Colour' AND i1.Value = 'Red'
  AND i2.FieldName = 'Length' AND i2.Value = '45'
  AND i3.FieldName = 'Shape' AND i3.Value = 'Square';

方法3：转宽表筛选（适合属性固定的场景） #

先把EAV结构转成常规的宽表（每个属性作为一列），再像普通表一样写条件筛选，可读性最好。

需求1实现：

WITH PivotedInfo AS (
    SELECT ItemId,
           MAX(CASE WHEN FieldName = 'Colour' THEN Value END) AS Colour,
           MAX(CASE WHEN FieldName = 'Length' THEN Value END) AS Length,
           MAX(CASE WHEN FieldName = 'Shape' THEN Value END) AS Shape
    FROM Info
    GROUP BY ItemId
)
SELECT ItemId
FROM PivotedInfo
WHERE Colour = 'Red' AND Length = '100';

需求2实现：

WITH PivotedInfo AS (
    SELECT ItemId,
           MAX(CASE WHEN FieldName = 'Colour' THEN Value END) AS Colour,
           MAX(CASE WHEN FieldName = 'Length' THEN Value END) AS Length,
           MAX(CASE WHEN FieldName = 'Shape' THEN Value END) AS Shape
    FROM Info
    GROUP BY ItemId
)
SELECT ItemId
FROM PivotedInfo
WHERE Colour = 'Red' AND Length = '45' AND Shape = 'Square';

内容的提问来源于stack exchange，提问作者Suggs