使用WITH ROLLUP时如何按SUM() DESC排序且汇总行在末尾？

需求明确：使用WITH ROLLUP生成包含汇总行的结果集，同时让普通分组行按SUM(sales)降序排列，且汇总行必须保留在结果末尾。

先看参考的表结构与测试数据：

CREATE TABLE booksales ( 
  country VARCHAR(35), genre ENUM('fiction','non-fiction'), year YEAR, sales INT);

INSERT INTO booksales VALUES
  ('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
  ('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
  ('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
  ('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);

原始WITH ROLLUP查询仅按分组字段排序，普通行未按SUM值降序：

SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP;

+------+------------+
| year | SUM(sales) |
+------+------------+
| 2014 |     173944 |
| 2015 |     180518 |
| NULL |     354462 |
+------+------------+

最优实现方案 #

核心思路是利用ROLLUP生成的汇总行中分组字段为NULL的特性，在ORDER BY中先区分普通行与汇总行，再对普通行按SUM值降序排序：

SELECT year, SUM(sales) AS total_sales
FROM booksales
GROUP BY year WITH ROLLUP
ORDER BY 
  -- 标记汇总行，让其排在最后
  CASE WHEN year IS NULL THEN 1 ELSE 0 END,
  -- 普通行按销售额总和降序排列
  total_sales DESC;

运行结果如下：

+------+-------------+
| year | total_sales |
+------+-------------+
| 2015 |      180518 |
| 2014 |      173944 |
| NULL |      354462 |
+------+-------------+

方案说明 #

• CASE WHEN year IS NULL THEN 1 ELSE 0 END：给汇总行标记为1，普通行标记为0，排序时0在前1在后，确保汇总行始终处于末尾。
• total_sales DESC：对普通行按销售额总和从高到低排序。
• 该方案无需额外子查询，直接在原ROLLUP查询后追加排序逻辑，性能最优，避免了额外的数据集处理。

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