mysql - 有没有更好的方法来获取具有所需属性和有效值的所有产品？

Question

我想从以下表结构中获取包含一组必需属性的所有产品:

product

uid | name
---------------
  1 | Product A
  2 | Product B
  3 | Product C


attribute_set

uid | product | attribute | attribute_value
-------------------------------------------
  1 |       1 |         2 |               4
  2 |       1 |         2 |               5
  3 |       1 |         3 |              10
  4 |       2 |         2 |               4
  5 |       2 |         3 |              10

例如，我想获取拥有值为 4 和 5 的属性 2 以及拥有值为 10 的属性 3 的所有产品。

为了实现此目的，我当前正在使用如下查询:

SELECT product.uid,
(
    SELECT COUNT(*)
    FROM attribute_set
    WHERE attribute_set.attribute = 2
    AND attribute_set.attribute_value IN (4,5)
    AND product.uid = attribute_set.product
) as counter,
(
    SELECT COUNT(*)
    FROM attribute_set
    WHERE attribute_set.attribute = 3
    AND attribute_set.attribute_value IN (10)
    AND product.uid = attribute_set.product
) as counter2
FROM product
LEFT JOIN attribute_set
ON product.uid = attribute_set.product
GROUP BY product.uid
HAVING counter = 2 AND counter2 = 1

所以我必须检查每个属性所需值的数量是否与子查询计数器值匹配。这对我有用，但是有没有办法做得更好？

Answer 1

试试这个，我通过删除子查询使用了 join 和 case

SELECT p.uid,
COUNT(CASE WHEN a.attribute = 2 AND a.attribute_value IN (4,5) THEN 1 END) AS counter1,
COUNT(CASE WHEN a.attribute = 3 AND a.attribute_value IN (10)  THEN 1 END) AS counter2
FROM product p
JOIN attribute_set a ON p.uid = a.product
GROUP BY p.uid
HAVING counter1 = 2 AND counter2 = 1