如何编写结合多列group by的pivot行转列SQL查询并实现分组小计

以下为兼容绝大多数SQL引擎的通用实现，核心采用CASE WHEN实现行转列，搭配ROLLUP生成区域分组小计：

静态日期实现（日期固定的场景） #

SELECT 
  area,
  COALESCE(canal, 'total') AS canal,
  SUM(CASE WHEN date = '2023-01-01' THEN peso ELSE 0 END) AS `2023-01-01`,
  SUM(CASE WHEN date = '2023-01-02' THEN peso ELSE 0 END) AS `2023-01-02`,
  SUM(CASE WHEN date = '2023-01-03' THEN peso ELSE 0 END) AS `2023-01-03`,
  SUM(peso) AS total_peso
FROM (
  -- 原有源查询作为子查询
  SELECT 
    t1.date, 
    t1.area, 
    t1.canal,
    SUM(t1.peso) AS peso
  FROM table1 t1
  GROUP BY 1, 2, 3
) t
GROUP BY area, ROLLUP(canal)
-- 过滤掉全表合计行
HAVING area IS NOT NULL
-- 保证每个区域的小计行排在该区域所有渠道行的末尾
ORDER BY area, CASE WHEN canal IS NULL THEN 1 ELSE 0 END, canal

注：代码中的日期值需替换为实际业务中的日期列表。

动态日期实现（日期不固定的场景） #

如果日期随业务动态变化，可拼接动态SQL执行，以MySQL为例：

-- 拼接行转列的字段部分
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN date = ''', date, ''' THEN peso ELSE 0 END) AS `', date, '`')
) INTO @sql
FROM table1;

-- 拼接完整查询语句
SET @sql = CONCAT('
SELECT 
  area,
  COALESCE(canal, ''total'') AS canal,
  ', @sql, ',
  SUM(peso) AS total_peso
FROM (
  SELECT t1.date, t1.area, t1.canal, SUM(t1.peso) AS peso FROM table1 t1 GROUP BY 1,2,3
) t
GROUP BY area, ROLLUP(canal)
HAVING area IS NOT NULL
ORDER BY area, CASE WHEN canal IS NULL THEN 1 ELSE 0 END, canal
');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

支持原生PIVOT语法的数据库简化写法 #

对于Oracle、SQL Server等支持原生PIVOT关键字的数据库，可简化行转列逻辑，以SQL Server为例：

SELECT 
  area,
  COALESCE(canal, 'total') AS canal,
  ISNULL([2023-01-01], 0) AS [2023-01-01],
  ISNULL([2023-01-02], 0) AS [2023-01-02],
  ISNULL([2023-01-03], 0) AS [2023-01-03],
  ISNULL([2023-01-01],0) + ISNULL([2023-01-02],0) + ISNULL([2023-01-03],0) AS total_peso
FROM (
  SELECT date, area, canal, peso FROM table1
) t
PIVOT (
  SUM(peso) FOR date IN ([2023-01-01], [2023-01-02], [2023-01-03])
) pvt
GROUP BY area, ROLLUP(canal)
HAVING area IS NOT NULL
ORDER BY area, CASE WHEN canal IS NULL THEN 1 ELSE 0 END, canal

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