MySQL GROUP BY场景下能否对CASE表达式的计算结果求和？

当然可以实现！不过你当前的写法有个关键问题——直接用PROD_NUM和CAT_NUM这两个别名在同一个SELECT子句里计算总和是行不通的。这是因为MySQL的查询执行顺序是先处理FROM/JOIN、WHERE、GROUP BY，之后才会解析SELECT里的列别名，所以在同一层SELECT里，别名还没生效，没法直接引用。

下面给你几种可行的解决方案：

方法1：重复CASE表达式计算总和 #

既然不能直接用别名，那我们可以把两个CASE表达式直接相加，虽然看起来有点冗余，但简单直接：

SELECT 
  CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END AS PROD_NUM,
  CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END AS CAT_NUM,
  -- 直接将两个CASE表达式相加得到总和
  (CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END + 
   CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END) AS TOTAL_NUM
FROM products 
JOIN categories ON products.category_id = categories.id -- 记得补充你的关联条件
GROUP BY products.color;

方法2：使用子查询/CTE先计算中间值 #

如果觉得重复写CASE太麻烦，可以用子查询或者CTE（MySQL 8.0及以上版本支持）先算出PROD_NUM和CAT_NUM，再在外层查询里求和：

子查询写法 #

SELECT 
  color,
  PROD_NUM,
  CAT_NUM,
  (PROD_NUM + CAT_NUM) AS TOTAL_NUM
FROM (
  SELECT 
    products.color,
    CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END AS PROD_NUM,
    CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END AS CAT_NUM
  FROM products 
  JOIN categories ON products.category_id = categories.id
) AS temp_table
GROUP BY color, PROD_NUM, CAT_NUM; -- GROUP BY需要包含所有非聚合列

CTE写法（更易读） #

WITH temp_table AS (
  SELECT 
    products.color,
    CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END AS PROD_NUM,
    CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END AS CAT_NUM
  FROM products 
  JOIN categories ON products.category_id = categories.id
)
SELECT 
  color,
  PROD_NUM,
  CAT_NUM,
  (PROD_NUM + CAT_NUM) AS TOTAL_NUM
FROM temp_table
GROUP BY color, PROD_NUM, CAT_NUM;

注意：如果是分组后的聚合求和 #

如果你的需求是对每个分组（比如每个color）的PROD_NUM和CAT_NUM分别求和后再相加，那需要用SUM()包裹CASE表达式，示例如下：

SELECT 
  products.color,
  SUM(CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END) AS PROD_NUM,
  SUM(CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END) AS CAT_NUM,
  -- 两种方式都可以：要么直接相加聚合后的结果，要么先相加再聚合
  SUM(CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END + 
      CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END) AS TOTAL_NUM
FROM products 
JOIN categories ON products.category_id = categories.id
GROUP BY products.color;

或者用子查询先算出分组后的聚合值，再在外层求和：

SELECT 
  color,
  PROD_NUM,
  CAT_NUM,
  (PROD_NUM + CAT_NUM) AS TOTAL_NUM
FROM (
  SELECT 
    products.color,
    SUM(CASE WHEN products.type=1 THEN 50 WHEN products.type=2 THEN 25 ELSE 0 END) AS PROD_NUM,
    SUM(CASE WHEN categories.id=4 THEN 12 WHEN categories.id=7 THEN 40 ELSE 5 END) AS CAT_NUM
  FROM products 
  JOIN categories ON products.category_id = categories.id
  GROUP BY products.color
) AS temp_table;

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