从表中获取数据,使用行作为列

我想您需要以下内容:

SELECT ProductId from myTable
 WHERE ProductId IN (SELECT ProductId FROM myTable WHERE FieldId = 50 AND FieldValue = '1.0')
   AND ProductId IN (SELECT ProductId FROM myTable WHERE FieldId = 55 AND FieldValue = 'Y') 
   AND ProductId IN (SELECT ProductId FROM myTable WHERE FieldId = 60 AND FieldValue = 'N')

(您可以将其中一个条件放在外部选择中,但我想这样更容易阅读。)

select distinct t1.productid
from mytable t1
inner join mytable t2 on t1.productid = t2.productid
inner join mytable t3 on t2.productid = t3.productid
where t1.fieldvalue = '1.0' and t1.fieldid = 50
and t2.fieldvalue = 'Y' and t2.fieldid = 55
and t3.fieldvalue = 'N' and t3.fieldid = 60

这个怎么样?

productID=101,fieldValue在('1.0'、'y'、'n')

[编辑]

也许您可以使用这样的子查询?

SELECT *
FROM MyTable
WHERE ProductId = 
-- SubQuery for searching ProductId based on FieldId and FieldValue
(SELECT TOP 1 ProductId
FROM MyTable
WHERE (FieldId = 50 AND (FieldValue BETWEEN '1.0' AND '1.0'))

一个记录的FieldValue不能同时有两个值,这就是为什么一个条件 x=1 and x=2 永远不会是真的。

你想用 or 条件之间:

ProductId = 101 and (FieldValue = '1.0' or FieldValue = 'Y' and FieldValue = 'N')

编辑:
如果必须找到包含fieldid和fieldvalue值组合的productid,则必须执行以下操作:

select * from MyTable
where ProductId = (
  select ProductId
  from MyTable m
  inner join MyTable m2 on m2.ProductId = m.ProductId and m2.FieldId = 55 and FieldValue = 'Y'
  inner join MyTable m3 on m3.ProductId = m.ProductId and m3.FieldId = 60 and FieldValue = 'N'
  where FieldId = 50 and FieldValue = '1.0'
)