如何简化带分类标题、明细及合计的交易分组报表生成？

当然有更高效的方法——用单条SQL结合UNION ALL就能一次性生成所需报表，无需循环多次查询。

实现逻辑 #

通过UNION ALL合并三类数据行，再按规则排序：

1. 分类标题行：提取唯一分类作为标题，其余字段留空
2. 明细行：直接查询原表的交易数据
3. 分类汇总行：按分类分组计算总金额，对应字段填充Total和汇总值
4. 可选空行：在不同分类之间添加空行，匹配你要的报表格式

最终SQL代码 #

SELECT 
    CASE 
        WHEN t.type = 1 THEN CONCAT('**', t.category_title, '**')
        WHEN t.type = 2 THEN CAST(t.id AS CHAR)
        ELSE ''
    END AS '',
    CASE WHEN t.type = 2 THEN t.dt_trans ELSE NULL END AS dt_trans,
    CASE WHEN t.type = 2 THEN t.category ELSE NULL END AS category,
    CASE 
        WHEN t.type = 3 THEN '**Total**'
        WHEN t.type = 2 THEN t.description
        ELSE NULL
    END AS description,
    CASE 
        WHEN t.type = 3 THEN t.total_amount
        WHEN t.type = 2 THEN t.amount
        ELSE NULL
    END AS amount
FROM (
    -- 1. 生成分类标题行
    SELECT DISTINCT
        category AS category_title,
        NULL AS id,
        NULL AS dt_trans,
        NULL AS category,
        NULL AS description,
        NULL AS amount,
        1 AS type,
        NULL AS total_amount,
        category AS sort_key
    FROM `transaction`
    
    UNION ALL
    
    -- 2. 生成交易明细行
    SELECT
        NULL AS category_title,
        id,
        dt_trans,
        category,
        description,
        amount,
        2 AS type,
        NULL AS total_amount,
        category AS sort_key
    FROM `transaction`
    
    UNION ALL
    
    -- 3. 生成分类汇总行
    SELECT
        NULL AS category_title,
        NULL AS id,
        NULL AS dt_trans,
        NULL AS category,
        NULL AS description,
        NULL AS amount,
        3 AS type,
        SUM(amount) AS total_amount,
        category AS sort_key
    FROM `transaction`
    GROUP BY category
    
    UNION ALL
    
    -- 4. 生成分类间的空行（不需要可删除此段）
    SELECT DISTINCT
        NULL AS category_title,
        NULL AS id,
        NULL AS dt_trans,
        NULL AS category,
        NULL AS description,
        NULL AS amount,
        4 AS type,
        NULL AS total_amount,
        category AS sort_key
    FROM `transaction`
) AS t
ORDER BY t.sort_key, t.type;

关键说明 #

• 用type字段标记行类型，确保排序时标题→明细→汇总→空行的顺序
• sort_key保证同一分类的所有行排在一起
• 如果不需要分类间的空行，直接删除第四个UNION ALL对应的子查询即可
• 标题和Total用**包裹，匹配你要的加粗格式（如果是在SQL工具中展示，可去掉CONCAT('**', ...)和'**Total**'的加粗标记）

内容的提问来源于stack exchange，提问作者bagi2info.com