mysql - 如何在条件聚合mysql中添加where？

Question

我的查询是这样的：

SELECT a.number, a.description, b.attribute_code, b.attribute_value 
FROM items a 
JOIN attr_maps b ON b.number = a.number WHERE a.number = AB123

如果执行查询，结果如下：

我想得到这样的结果：

我使用这样的条件聚合：

SELECT i.number, i.description,
       MAX(CASE WHEN am.attribute_code = 'brand' then am.attribute_value END) as brand,
       MAX(CASE WHEN am.attribute_code = 'model' then am.attribute_value END) as model,
       MAX(CASE WHEN am.attribute_code = 'category' then am.attribute_value END) as category,
       MAX(CASE WHEN am.attribute_code = 'subcategory' then am.attribute_value END) as subcategory
FROM items i JOIN
     attr_maps am
     ON am.number = i.number
WHERE i.number = AB123
GROUP BY i.number, i.description

有用

但我仍然对添加where条件感到困惑。所以我希望它可以按品牌等过滤

我尝试这样：

SELECT i.number, i.description,
       MAX(CASE WHEN am.attribute_code = 'brand' then am.attribute_value END) as brand,
       MAX(CASE WHEN am.attribute_code = 'model' then am.attribute_value END) as model,
       MAX(CASE WHEN am.attribute_code = 'category' then am.attribute_value END) as category,
       MAX(CASE WHEN am.attribute_code = 'subcategory' then am.attribute_value END) as subcategory
FROM items i JOIN
     attr_maps am
     ON am.number = i.number
WHERE brand = 'honda'
GROUP BY i.number, i.description

存在错误Unknown column 'brand' in 'where clause'

我该如何解决这个错误？

标签： mysqlsqljoinpivotaggregate

Answer 1

更改查询的最后一部分

WHERE brand = 'honda'
GROUP BY i.number, i.description

至

GROUP BY i.number, i.description
HAVING brand = 'honda'