MySQL如何按分类统计优先取促销价的商品最高最低售价

需要实现按商品分类统计有效售价的最小值、最大值，价格取值规则：

• 商品存在有效促销价（promo_cost不为0）时，取促销价作为有效售价
• 无有效促销价时，取常规售价cost作为有效售价

涉及的products表结构：

CREATE TABLE `products` (
`id` int(11) NOT NULL,
`name` varchar(155) NOT NULL,
`category_id` varchar(25) NOT NULL,
`cost` decimal(65,2) NOT NULL,
`promo_cost` decimal(62,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

原有SQL仅支持分类下商品全有促销价、或全无促销价的场景，无法适配两类商品混存的情况，原语句如下：

SELECT 
 CASE 
  WHEN promo_cost != 0 
  THEN MAX(promo_cost) 
  ELSE MAX(cost) END as max_price 
FROM products WHERE category_id = $category_id

测试场景 #

某分类下共3件商品：

• 商品1：cost=50.00，promo_cost=30.00
• 商品2：cost=60.00，promo_cost=40.00
• 商品3：cost=20.00，promo_cost=0.00
  预期统计结果：最高售价40.00，最低售价20.00

原语句的核心问题是将条件判断写在了聚合函数外层，是对分组整体做判断，无法逐行计算单个商品的有效售价。只要把价格判断逻辑下沉到行级，先算出每个商品的实际有效售价，再做最值聚合即可。
单分类查询的SQL写法：

SELECT
  MAX(CASE WHEN promo_cost <> 0 THEN promo_cost ELSE cost END) AS max_price,
  MIN(CASE WHEN promo_cost <> 0 THEN promo_cost ELSE cost END) AS min_price
FROM products
WHERE category_id = $category_id;

如果需要一次性统计全部分类的价格区间，加上分组即可：

SELECT
  category_id,
  MAX(CASE WHEN promo_cost <> 0 THEN promo_cost ELSE cost END) AS max_price,
  MIN(CASE WHEN promo_cost <> 0 THEN promo_cost ELSE cost END) AS min_price
FROM products
GROUP BY category_id;

注：MySQL环境下也可以用IF(promo_cost <> 0, promo_cost, cost)替换CASE写法，效果完全一致。

以上写法针对给出的测试场景，会逐行计算有效售价：3件商品的有效售价分别为30.00、40.00、20.00，最终聚合得到max_price=40.00、min_price=20.00，完全符合预期，同时兼容全有促销、全无促销、混合存在的所有场景。

内容的提问来源于stack exchange，提问作者bobi