SQL语句如何获取同时满足多个属性的数据

产品存在多个属性且是以行的形式，该问题主要就是要获取同时满足多个条件的产品记录。

原问题（原文链接）：

There is a table cs_goods_features like this (id – product id, f – product property name, v – property value)

I need to select only those products id, which have, for example, f1 = 10 and f2 = 15.

If I make a query like this

SELECT id 
  FROM cs_goods_features
  where (f in ('f1', 'f2'))
    and (v in (10,15))

then everything is fine except when the table has opposite values – not f1=10 and f2=15, but f1=15 and f2=10. I don’t need such lines in result set.

What I need can be done like this:

select g1.id, g2.id
   FROM cs_goods_features g1, cs_goods_features g2 
 WHERE g1.f = 'f1'
   and g1.v = 10 
   and g2.f = 'f2'
   and g2.v = 15

采纳答案

One way to do it is by counting for each id the number of times (f1, 10) and (f2, 15) appear and selecting distinct id where they appear at least once each:

select distinct id from
(select *,
sum(case when f = 'f1' and v = 10 then 1 else 0 end) over(partition by id) as f1_10,
sum(case when f = 'f2' and v = 15 then 1 else 0 end) over(partition by id) as f2_15
from cs_goods_features) t
where f1_10 > 0 and f2_15 > 0

问题解析：

cs_goods_features表中存在id、f、v这三个字段，其中id表示产品id，f表示属性，v表示具体的属性值。现在的问题是需要查找某个产品同时具有f1、f2这两个属性值，且f1=10，f2=15。

SQL语句的思路便是将属性f与属性值v组合，然后再将f1=10，f2=15这两个条件用且的关系查询。如果属性都是以列的形式展示这SQL语句十分简单，处理的时候也可以先考虑处理行转列然后再查询。处理后的形式为：id、f1、f2、f3。

一般刚开始容易想到下面的查询语句：

SELECT id
FROM cs_goods_features
WHERE (f = 'f1' AND v = 10)
OR (f = 'f2' AND v = 15)

该语句之所以不对，主要就在于忽略了这个产品需要同时满足两个条件。比如将id=3的是f2属性改成15，则id为3也会查询出来，这是不满足要求的，我将id=3的数据稍微修改了下，具体可以查看 dbfiddle