MySQL 行转列与列转行教程：季度报表、空分类和条件聚合

销售表按一笔一行保存，报表却要“分类、一月、二月、三月、合计”这样的横表。MySQL 中固定列的行转列，可以用 SUM(CASE WHEN ... THEN ... ELSE ... END) 完成。真正容易算错的地方通常不在 CASE 语法，而在于同月多笔是否累加、没有订单的分类是否保留，以及时间范围有没有漏掉月末数据。

这次直接做一张 2026 年第一季度分类报表。SQL 适用于 MySQL 8.0.16 及以上，下面的数据、查询和反例在 MySQL 8.4.11 上执行过。金额使用 DECIMAL，时间字段明确为业务统一口径的 DATETIME，不混入隐式时区转换。

同一批明细，先把应该得到的答案算清楚

在自己的练习库执行完整建表和数据初始化：

CREATE DATABASE pivot_practice CHARACTER SET utf8mb4;
USE pivot_practice;

CREATE TABLE product_category (
    id BIGINT NOT NULL,
    category_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE sale_detail (
    id BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    sold_at DATETIME(6) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    status VARCHAR(12) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_category_status_time (category_id, status, sold_at),
    CONSTRAINT fk_sale_category FOREIGN KEY (category_id)
        REFERENCES product_category(id),
    CONSTRAINT chk_sale_amount CHECK (amount >= 0),
    CONSTRAINT chk_sale_status CHECK (status IN ('paid', 'cancelled'))
) ENGINE=InnoDB;

INSERT INTO product_category VALUES
(10, '数码'), (20, '图书'), (30, '食品');

INSERT INTO sale_detail VALUES
(1, 10, '2026-01-05 10:00:00', 100.00, 'paid'),
(2, 10, '2026-01-21 12:00:00',  50.00, 'paid'),
(3, 10, '2026-02-03 09:00:00',  80.00, 'paid'),
(4, 10, '2026-03-31 23:59:59.999999', 120.00, 'paid'),
(5, 20, '2026-01-12 08:00:00',  25.00, 'paid'),
(6, 20, '2026-03-08 15:00:00',  75.00, 'paid'),
(7, 10, '2026-03-10 09:00:00', 999.00, 'cancelled'),
(8, 10, '2026-04-01 00:00:00', 500.00, 'paid'),
(9, 20, '2025-12-31 23:59:59',   9.00, 'paid');

数码一月有两笔，合计 150；二月 80，三月 120，季度 350。图书是一月 25、三月 75，季度 100。食品没有明细，但分类仍需在报表中显示三个零。整张季度报表应合计 450，取消记录、上年十二月和四月一日都不参与。

对应查询：

SELECT c.id, c.category_name,
       SUM(CASE WHEN s.sold_at < '2026-02-01'
                THEN s.amount ELSE 0 END) AS jan_amount,
       SUM(CASE WHEN s.sold_at >= '2026-02-01'
                 AND s.sold_at <  '2026-03-01'
                THEN s.amount ELSE 0 END) AS feb_amount,
       SUM(CASE WHEN s.sold_at >= '2026-03-01'
                THEN s.amount ELSE 0 END) AS mar_amount,
       COALESCE(SUM(s.amount), 0) AS quarter_amount,
       COUNT(s.id) AS detail_count
FROM product_category AS c
LEFT JOIN sale_detail AS s
  ON s.category_id = c.id
 AND s.status = 'paid'
 AND s.sold_at >= '2026-01-01'
 AND s.sold_at <  '2026-04-01'
GROUP BY c.id, c.category_name
ORDER BY c.id;

结果按分类编号排列：数码 150.00 / 80.00 / 120.00 / 350.00 / 4，图书 25.00 / 0.00 / 75.00 / 100.00 / 2，食品 0.00 / 0.00 / 0.00 / 0.00 / 0。

这里的 detail_count 是销售明细行数，不是去重订单数或购买人数。现实中一张订单可能有多个商品行，不能因为每行都有主键，就把四行直接叫作四个订单。报表增加人数、客单价之前，要先明确事实表的一行代表什么，再选择相应的去重键。

每个 CASE 是把“当前明细是否属于这一月”转成一个金额，再由 SUM 累加。它没有修改表结构，也没有把明细物理搬进新列。季度总范围已经放在连接条件里，因此一月分支只需要比较二月之前，三月分支只需要比较三月起点；如果把这些分支单独搬到别的查询，外层时间范围也必须一起保留。

本表把 amount 定为非空，因此“这个月没有有效销售”可以显示成零。如果来源系统用 NULL 表示金额尚未确认，就不能在接入时先全部改成零，否则“未知”会被伪装成“已经确认没有收入”。CASE 的 ELSE 0 解决的是当前明细不属于该月，不替业务决定缺失数据的含义。

上界用四月一日的排他边界，能包含三月最后一天的微秒。写成 <= '2026-03-31'，比较的是这天零点，会漏掉当天后续记录。只写 MONTH(sold_at)=1 也不能区分不同年份。这里先按原始时间字段圈定完整季度，索引也围绕分类、状态和时间组织；小样本是否选择该索引由优化器决定，不能凭建了索引就声称已经加速。

两个“看起来像行转列”的错误结果

如果给一月列换成 MAX，同月多笔明细不会报错，却会悄悄少算：

SELECT category_id,
       MAX(CASE WHEN sold_at < '2026-02-01'
                THEN amount ELSE 0 END) AS wrong_jan,
       SUM(CASE WHEN sold_at < '2026-02-01'
                THEN amount ELSE 0 END) AS correct_jan
FROM sale_detail
WHERE status = 'paid'
  AND sold_at >= '2026-01-01'
  AND sold_at <  '2026-04-01'
GROUP BY category_id
ORDER BY category_id;

数码会得到 100.00 / 150.00。MAX 适用于“每组该维度已经只有一个有效值，选出它”的问题，本表的口径是累计销售额，必须 SUM。相反，如果源表本应每分类每月只有一条记录，出现重复就应先检查唯一约束或上游数据，而不是随手用 MAX 掩盖重复。

另一个错误是明明用了 LEFT JOIN，却把右表条件放到 WHERE：

SELECT c.id, COUNT(s.id) AS detail_count
FROM product_category AS c
LEFT JOIN sale_detail AS s ON s.category_id = c.id
WHERE s.status = 'paid'
  AND s.sold_at >= '2026-01-01'
  AND s.sold_at <  '2026-04-01'
GROUP BY c.id
ORDER BY c.id;

这条只剩数码和图书。食品连接出来的右侧为 NULL，过不了 WHERE。是否展示零销售分类是报表需求的一部分；要保留它，就从分类表出发，并在 ON 中限定符合口径的销售明细。

计数也要用对表达式。下面故意并排给出两个写法：

SELECT c.id,
       COUNT(CASE WHEN s.sold_at < '2026-02-01'
                  THEN 1 ELSE 0 END) AS wrong_jan_count,
       COUNT(CASE WHEN s.sold_at < '2026-02-01'
                  THEN 1 END) AS correct_jan_count
FROM product_category AS c
LEFT JOIN sale_detail AS s
  ON s.category_id = c.id
 AND s.status = 'paid'
 AND s.sold_at >= '2026-01-01'
 AND s.sold_at <  '2026-04-01'
GROUP BY c.id
ORDER BY c.id;

COUNT 统计非 NULL，零也会计入。数码是 4 / 2，食品甚至是 1 / 0：外连接为食品保留了一行，ELSE 0 把这行也数进去了。数量可以用省略 ELSE 的 COUNT(CASE...)，或者 SUM(CASE...THEN 1 ELSE 0 END)；总明细数用非空主键 COUNT(s.id)，不要把 COUNT(*) 当成右表记录数。

把横表再交给折线图：列转行

接口的展示不总是 Excel 横表。图表常需要“分类、月份、金额”这样的长表。下面先在 CTE 中形成三个月的横表，再用 UNION ALL 展开；这是完整独立语句，不依赖先执行某个临时表。

WITH monthly AS (
    SELECT c.id, c.category_name,
           SUM(CASE WHEN s.sold_at < '2026-02-01'
                    THEN s.amount ELSE 0 END) AS jan_amount,
           SUM(CASE WHEN s.sold_at >= '2026-02-01'
                     AND s.sold_at < '2026-03-01'
                    THEN s.amount ELSE 0 END) AS feb_amount,
           SUM(CASE WHEN s.sold_at >= '2026-03-01'
                    THEN s.amount ELSE 0 END) AS mar_amount
    FROM product_category AS c
    LEFT JOIN sale_detail AS s
      ON s.category_id = c.id
     AND s.status = 'paid'
     AND s.sold_at >= '2026-01-01'
     AND s.sold_at <  '2026-04-01'
    GROUP BY c.id, c.category_name
)
SELECT id, category_name, '2026-01' AS month_key, jan_amount AS amount
FROM monthly
UNION ALL
SELECT id, category_name, '2026-02', feb_amount FROM monthly
UNION ALL
SELECT id, category_name, '2026-03', mar_amount FROM monthly
ORDER BY id, month_key;

它返回九行，三个分类都带齐三个月，金额仍合计 450。保留零值能让折线图连续展示月份；如果业务要隐藏全零分类，应在分类级别判断整个季度，而不是逐行过滤 amount=0，把某些月份也删掉。

这是聚合结果的列转行，不会还原最初九条销售明细。数码一月的 150 已经丢掉了原来 100 和 50 的分笔信息，任何“反转”语句都不能凭空恢复它。

把这张报表改成第二季度时，要同时调整整体范围、三个 CASE 的月界和输出月份文本。只改最外层起止日期，留下旧月界，依旧会产生合法但错误的 SQL。核对时除了看季度总额，也应检查每个分类横向三列之和等于该分类合计，并保留一条落在结束边界、一条落在结束前微秒的样本。单看总计没有变，无法证明月份分配正确。

固定季度报表的列名是接口合同的一部分，明确写三列反而便于维护。若月份由用户任意选择，通常可以直接返回按分类、月份 GROUP BY 的长表，让前端生成横向列。确实要动态生成 SQL 时，列名只能来自后端允许的月份集合，值仍用参数绑定；不要把用户输入直接拼成标识符，也不要用 GROUP_CONCAT 拼出 SQL 后就默认已经解决了长度、转义和权限问题。