MySQL 查询过滤结果

Question

下面是我的数据表，

1) 产品

id    name    
------------
1     abc
2     def
3     xyz

2) product_attribute

id    product_id  attribute_id  attribute_value_id    
--------------------------------------------------
1     1           1             1
2     1           1             2
3     1           2             4
4     2           1             3
5     2           2             5
6     3           1             1
7     1           3             6

3) 属性

id    name
---------------
1     color        
2     size
3     width

4) 属性值

id    name
--------------
1     Red
2     Blue
3     Black
4     2.5
5     3.5
6     5

当找到 color = red 的产品时，得到产品 'abc' 和 'xyz。

SELECT * 
FROM product AS p 
INNER JOIN product_attribute AS pa ON pa.p_id = p.id 
WHERE (pa.attribute_id = 1 AND pa.attribute_value_id IN(1)) 
GROUP BY p.id

问题

当找到颜色 = 红色且尺寸 = 2.5 的产品时，返回零结果。

SELECT * 
FROM product AS p 
INNER JOIN product_attribute AS pa ON pa.p_id = p.id 
WHERE ((pa.attribute_id = 1 AND pa.attribute_value_id IN(1)) 
  AND (pa.attribute_id = 2 AND pa.attribute_value_id IN(4))) 
GROUP BY p.id

如何获得颜色 = 红色且尺寸 = 2.5 的产品？

Answer 1

这里有两种使用连接获取匹配所有这些属性和值的产品的方法

使用计数()

SELECT p.id,p.name  
FROM product p 
INNER JOIN product_attribute pa ON pa.product_id = p.id 
INNER JOIN attribute a ON a.id = pa.attribute_id
INNER JOIN attribute_value av ON av.id = pa.attribute_value_id
WHERE a.name IN('color','size')
AND av.name IN('red','2.5')
GROUP BY p.id,p.name
HAVING COUNT(DISTINCT a.id) = 2 
AND COUNT(DISTINCT av.id) = 2

通过使用 sum()

SELECT p.id,p.name 
FROM product p 
INNER JOIN product_attribute pa ON pa.product_id = p.id 
INNER JOIN attribute a ON a.id = pa.attribute_id
INNER JOIN attribute_value av ON av.id = pa.attribute_value_id
GROUP BY p.id,p.name 
HAVING SUM(a.name = 'color' AND av.name = 'red') > 0
AND SUM(a.name = 'size' AND av.name = '2.5') > 0

DEMO