根据多个条件筛选表，其中项目属性作为单独的行输入

Question

我继承了SQL Server表设计，如下所述。该表称为Info。

我需要理解返回以下两个示例的正确结果所需的SQL语句。

Id  | ItemId | FieldName | Value
1  | 302    | Colour    | Red
2  | 303    | Length    | 100
3  | 304    | Length    | 25
4  | 305    | Colour    | Blue
5  | 306    | Colour    | Blue
6  | 306    | Length    | 100
7  | 307    | Colour    | Blue
8  | 307    | Length    | 35
9  | 308    | Colour    | Red
10 | 308    | Length    | 100
11 | 309    | Colour    | Red
12 | 309    | Length    | 45
13 | 309    | Shape     | Square
14 | 310    | Shape     | Round

示例1：我需要找出哪些项目(ItemId(的颜色为"0"；红色"；与长度"；100〃；从"；信息"；表-应仅为，ItemId 308

我有限的SQL知识使我走上了的道路

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

但是这返回项目302和308，类似于的查询

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

当正确的输出应该只是ItemId 308 时，返回303和308

我的场景可能还需要扩展此查询，以包括以下示例中定义的两个以上字段：

示例2：我需要找出哪些物品的颜色为"；红色"；与长度"；45〃；AND是"；正方形"-应该只有ItemId 309

如有任何建议，我们将不胜感激。

Answer 1

我认为这将达到您的要求。我使用LEFT OUTER JOINs来联接您正在搜索的每个参数。然后在顶部，我使用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
;

Answer 2

这是一个经典的带有余数的关系除法问题

有很多解决方案，但如果你想让它变得灵活，并能够处理不同数量的道具，那么你需要规范化你的输入。您可以使用表变量、临时表或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);

Answer 3

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

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 

值颜色长度颜色[/tr>长度形状

Id	ItemId	FieldName
9	308	红色
10	308	100
11	309	红色
12	309	45
13	309	方形

第一个例子：

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

ItemId
308

我们可以进行透视并简单地检索信息。

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'

长度方形

ItemId	颜色	形状
308	红色	100	空
309	红色	45

试试这个：

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