mysql - 在 where 子句中检查 max( CASE WHEN ) 的值 - 编辑：HAVING CLAUSE

Question

我有这个来自 wordpress 表的查询。我正在用其他数据库在 wordpress 之外构建一个 api。我只想让产品进货 。

所以这个 - max case when - 可以给出两个值，instock或outofstock。

max( CASE WHEN pm.meta_key = "_stock_status" and product_id = pm.post_id THEN pm.meta_value END ) as product_stock_status

如何仅获取此查询中的库存值？

完整查询：

SELECT product_id, product_language, product_name, 
max( CASE WHEN pm.meta_key = "_price" and product_id = pm.post_id THEN pm.meta_value END ) as product_price, 
max( CASE WHEN pm.meta_key = "_stock_status" and product_id = pm.post_id THEN  pm.meta_value END ) as product_stock_status
FROM search_table
INNER JOIN postmeta pm ON product_id = pm.post_id
WHERE product_language LIKE "en"
GROUP BY product_id
ORDER BY RAND() LIMIT 15

我希望获得带有product_stock_status instock的随机产品。

---

编辑：解决方案

在 GROUP BY 之后使用 HAVING 子句，即 HAVING product_stock_status = 'instock' – Nick

SELECT product_id, product_language, product_name, 
max( CASE WHEN pm.meta_key = "_price" and product_id = pm.post_id THEN pm.meta_value END ) as product_price, 
max( CASE WHEN pm.meta_key = "_stock_status" and product_id = pm.post_id THEN  pm.meta_value END ) as product_stock_status
FROM search_table
INNER JOIN postmeta pm ON product_id = pm.post_id
WHERE product_language LIKE "en"
GROUP BY product_id
HAVING product_stock_status = 'instock'
ORDER BY RAND() LIMIT 15

标签： mysqlsqlmaxcasecase-when