选择在两个不同行中具有某些值的所有部件号

If I do a query:

如果我查询：

SELECT PartNumber FROM Product_Options;

SELECT ProductNumber FROM Product_Options;

I get a result like this:

我得到这样的结果：

What I really need to do is select all from Product_Options where OptionName = 'Category' AND OptionValue = 'KID', OptionName = 'Category' AND OptionName = 'SKT'. OptionName must have those values in the SAME row.

我真正需要做的是从Product_Options中选择所有，其中OptionName ='Category'和OptionValue ='KID'，OptionName ='Category'和OptionName ='SKT'。 OptionName必须在SAME行中具有这些值。

I now I'm probably way off here but the query I tried resulted in a syntax error:

我现在可能在这里，但我尝试的查询导致语法错误：

SELECT PartNumber FROM Product_Options WHERE OptionName = 'Category' AND OptionValue = 'KID', OptionName = 'Category' AND OptionValue = 'SKT';

SELECT PartNumber FROM Product_Options WHERE OptionName ='Category'AND OptionValue ='KID'，OptionName ='Category'AND OptionValue ='SKT';

If it actually worked, the above query would return 'CR5661' and whichever other PartNumbers had an OptionValue of KID and OptionValue of SKT

如果它实际工作，上面的查询将返回'CR5661'和任何其他PartNumbers具有KID的OptionValue和SKT的OptionValue

I'm not sure if I need to use a JOIN, but the idea is to select all part numbers where the category is KID and the category is also SKT all having the same PartNumber.

我不确定是否需要使用JOIN，但想法是选择类别为KID的所有部件号，并且类别也是SKT都具有相同的PartNumber。

1 个解决方案

#1

You can use count(distinct OptionValue) or count(*) if OptionValue is unique for each PartNumber and use the where to restrict the source to just the values you are looking for.

如果OptionValue对于每个PartNumber是唯一的，则可以使用count（不同的OptionValue）或count（*），并使用where将源限制为您正在查找的值。

select PartNumber
from Product_Options
where OptionName = 'Category' 
  and (OptionValue = 'KID' or OptionValue = 'SKT')
group by PartNumber
having count(distinct OptionValue)>1