请求编写MySQL行转列查询，含日期列、均值及总计

你好！作为MySQL新手，要实现这种**行转列（Pivot）**的报表格式完全没问题，下面我会给你两种可行的方案，分别适配固定日期和动态日期的场景，同时满足你需要的千分位、平均值和总计行需求。

首先先明确你的原始数据（我帮你整理成表格格式更清晰）：

原始数据 #

你期望的输出格式（已修正日期重复的笔误） #

方案一：静态日期列查询（适合日期固定的场景） #

如果你的日期范围是固定的（比如就是2018-05-01至2018-05-06），直接用CASE WHEN配合聚合函数就能实现，同时处理千分位和计算：

-- 替换your_table为你的实际表名
SELECT
    ENV AS MFG,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-01' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-01`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-02' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-02`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-03' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-03`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-04' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-04`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-05' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-05`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-06' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-06`,
    FORMAT(AVG(SUM_TRX), 0) AS `Avg count day`
FROM your_table
GROUP BY ENV
UNION ALL
-- 添加总计行
SELECT
    'TOTAL:' AS MFG,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-01' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-01`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-02' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-02`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-03' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-03`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-04' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-04`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-05' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-05`,
    FORMAT(SUM(CASE WHEN TRX_DATE = '2018-05-06' THEN SUM_TRX ELSE 0 END), 0) AS `2018-05-06`,
    '' AS `Avg count day`
FROM your_table;

关键说明： #

• CASE WHEN：针对每个日期筛选对应的数据，用SUM聚合得到该日期的总数值
• FORMAT()：自动添加千分位分隔符，匹配你期望的格式
• UNION ALL：将明细行和总计行拼接在一起，总计行的平均值列留空
• 注意：mfg_Stress在2018-05-05有两行数据，SUM会自动合并这两个值，符合报表逻辑

方案二：动态日期列查询（适合日期不固定的场景） #

如果你的日期范围会变化，不想每次手动修改SQL，可以用动态SQL自动生成所有日期列：

SET @sql = NULL;
-- 自动生成每个日期对应的CASE WHEN语句
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'FORMAT(SUM(CASE WHEN TRX_DATE = ''',
            TRX_DATE,
            ''' THEN SUM_TRX ELSE 0 END), 0) AS `',
            TRX_DATE,
            '`'
        )
    ) INTO @sql
FROM your_table;

-- 拼接完整的SQL语句
SET @sql = CONCAT('
SELECT
    ENV AS MFG,
    ', @sql, ',
    FORMAT(AVG(SUM_TRX), 0) AS `Avg count day`
FROM your_table
GROUP BY ENV
UNION ALL
SELECT
    ''TOTAL:'' AS MFG,
    ', @sql, ',
    '''' AS `Avg count day`
FROM your_table
');

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

关键说明： #

• GROUP_CONCAT：自动遍历表中所有唯一的TRX_DATE，生成对应的列处理语句
• 预处理语句：动态生成的SQL需要用PREPARE和EXECUTE执行
• 优势：无需手动维护日期列，自动适配表中所有日期

额外提示： #

1. 你期望的结果里有一处日期重复（2018-05-02出现两次），我已经在方案中修正为正确的日期序列
2. 如果使用MySQL 8.0+，也可以尝试用窗口函数优化平均值计算，但上面的方案适配所有MySQL版本
3. 确保你的表名和字段名和SQL中的一致，记得替换your_table为实际表名

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