MySQL实现按姓名分组的月度累计金额自定义格式列查询方法

实现前提 #

MySQL版本≥8.0（支持CTE与窗口函数），如果是更低版本需要调整为子查询+自连接的写法。

完整查询语句 #

-- 构造月份序号映射表，确保月份排序正确，可根据实际数据的月份写法调整
WITH month_mapping AS (
    SELECT 'Jan' AS month_str, 1 AS month_seq UNION ALL
    SELECT 'Feb' AS month_str, 2 AS month_seq UNION ALL
    SELECT 'Feb.' AS month_str, 2 AS month_seq UNION ALL -- 兼容示例里带点的Feb.
    SELECT 'March' AS month_str, 3 AS month_seq UNION ALL
    SELECT 'April' AS month_str, 4 AS month_seq UNION ALL
    SELECT 'May' AS month_str, 5 AS month_seq UNION ALL
    SELECT 'June' AS month_str, 6 AS month_seq UNION ALL
    SELECT 'July' AS month_str, 7 AS month_seq UNION ALL
    SELECT 'Aug' AS month_str, 8 AS month_seq UNION ALL
    SELECT 'Sep' AS month_str, 9 AS month_seq UNION ALL
    SELECT 'Oct' AS month_str, 10 AS month_seq UNION ALL
    SELECT 'Nov' AS month_str, 11 AS month_seq UNION ALL
    SELECT 'Dec' AS month_str, 12 AS month_seq
),
-- 给原表数据关联月份序号
table_with_seq AS (
    SELECT 
        t.Name,
        t.Amount,
        t.month,
        m.month_seq
    FROM 你的实际表名 t
    INNER JOIN month_mapping m ON t.month = m.month_str
),
-- 计算每个用户首次出现的月份序号，用于生成首月前的补0内容
user_first_month AS (
    SELECT 
        Name,
        MIN(month_seq) AS first_seq
    FROM table_with_seq
    GROUP BY Name
)
-- 主查询，拼接要求格式的新列
SELECT 
    t.Name,
    t.Amount,
    t.month,
    CASE
        -- 首月逻辑
        WHEN t.month_seq = u.first_seq THEN
            CONCAT(
                '(',
                REPEAT('0+', u.first_seq - 1),
                t.month,
                ')=',
                SUM(t.Amount) OVER(PARTITION BY t.Name ORDER BY t.month_seq ASC)
            )
        -- 非首月逻辑
        ELSE
            CONCAT(
                '(',
                REPEAT('0+', u.first_seq - 1),
                GROUP_CONCAT(prev.Amount ORDER BY prev.month_seq ASC SEPARATOR '+'),
                ')=',
                SUM(t.Amount) OVER(PARTITION BY t.Name ORDER BY t.month_seq ASC)
            )
    END AS `new column`
FROM table_with_seq t
INNER JOIN user_first_month u ON t.Name = u.Name
LEFT JOIN table_with_seq prev ON t.Name = prev.Name AND prev.month_seq <= t.month_seq
GROUP BY t.Name, t.Amount, t.month, t.month_seq, u.first_seq
ORDER BY t.Name, t.month_seq ASC;

注意事项 #

1. 请把代码中的你的实际表名替换为你自己的数据表名
2. 如果你的月份字段存储格式和示例不同（比如是全拼、数字等），只需要调整month_mapping里的month_str值和实际数据匹配即可
3. 如果用户跨多年统计，可以在分组、排序逻辑里补充年份字段即可适配

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