MySQL透视表中MAX函数作用解析：为何添加MAX能得到正确结果？

原始表结构与数据 #

表名：table_one

期望的透视表结果 #

两次SQL尝试 #

Attempt #1 #

SELECT 
    fk_tbl_id, 
    CASE types WHEN 'type_a' THEN values END AS type_a,
    CASE types WHEN 'type_b' THEN values END AS type_b
FROM table_one
GROUP BY fk_tbl_id;

执行结果（不符合预期）

Attempt #2 #

SELECT 
    fk_tbl_id, 
    MAX(CASE types WHEN 'type_a' THEN values END) AS type_a,
    MAX(CASE types WHEN 'type_b' THEN values END) AS type_b
FROM table_one
GROUP BY fk_tbl_id;

执行结果（符合预期）

与期望的透视表结果完全一致。

原理解释 #

核心要搞懂GROUP BY和聚合函数的工作逻辑：

1. 无聚合函数的GROUP BY逻辑：
   GROUP BY fk_tbl_id会把相同fk_tbl_id的行归为一组，每组最终只返回一行结果。如果没有聚合函数，数据库会从组内的行中随机选取一行的字段值返回。比如fk_tbl_id=10的组有两行：

   • 第一行：type_a为value_a，type_b为null
   • 第二行：type_a为null，type_b为value_b
     数据库只取了其中一行（这里是第一行），所以得到的type_b是null，不符合预期。

2. 添加MAX()后的逻辑：
   MAX()是聚合函数，它会遍历组内的所有行，对指定字段做计算后返回结果。对于MAX(CASE types WHEN 'type_a' THEN values END)：

   • 组内只有一行能通过CASE得到有效值（比如value_a），其他行的CASE结果都是null
   • MAX()函数会自动忽略null，直接取到那个唯一的有效值
     同理，MAX(CASE ... type_b ...)会取到组内type_b对应的有效值value_b。这样每组就能把分散在不同行的两个值，整合到同一行的不同列中，正好实现透视表的效果。

其实不止MAX()，用MIN()（字符串或数值类型都适用）、SUM()（仅数值类型）也能达到同样效果，核心都是利用聚合函数遍历组内所有行、忽略null的特性，完成行转列的整合。

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