mysql - 从mysql中选择不同键值

Question

我想编写一些查询，为我提供具有特定键值的产品

tbl_产品

  product_id  field_id    value
      1          1         100
      1          2         200

我想查找具有 (field_id = 1 and value = 100) 和 (field_id = 2 and value = 200 ) 的所有产品

Answer 1

您可以使用 where、group by 和 having:

select product_id
from t
where (field_id = 1 and value = 100) or (field_id = 2 and value = 200 )
group by product_id
having count(*) = 2;

如果允许重复，那么您需要 count(distinct field_id) = 2。