如何修改MySQL查询以获取透视表（Pivot View）形式的数据

静态日期透视（固定过去7天场景） #

如果业务中过去7天的日期范围固定，可直接通过CASE WHEN将日期维度转为横向列：

SELECT
    t2.table_name,
    t2.AVG_XDR AS `Avg(XDR)`,
    t2.Threshold,
    MAX(CASE WHEN t1.table_date = DATE(NOW() - INTERVAL 6 DAY) THEN t1.xdr_count END) AS `6天前`,
    MAX(CASE WHEN t1.table_date = DATE(NOW() - INTERVAL 5 DAY) THEN t1.xdr_count END) AS `5天前`,
    MAX(CASE WHEN t1.table_date = DATE(NOW() - INTERVAL 4 DAY) THEN t1.xdr_count END) AS `4天前`,
    MAX(CASE WHEN t1.table_date = DATE(NOW() - INTERVAL 3 DAY) THEN t1.xdr_count END) AS `3天前`,
    MAX(CASE WHEN t1.table_date = DATE(NOW() - INTERVAL 2 DAY) THEN t1.xdr_count END) AS `2天前`,
    MAX(CASE WHEN t1.table_date = DATE(NOW() - INTERVAL 1 DAY) THEN t1.xdr_count END) AS `1天前`,
    MAX(CASE WHEN t1.table_date = DATE(NOW()) THEN t1.xdr_count END) AS `今日`
FROM stat t1
INNER JOIN (
    SELECT 
        table_name, 
        AVG(xdr_count) AS AVG_XDR,
        AVG(xdr_count)*0.8 AS Threshold
    FROM stat
    WHERE table_date >= DATE(NOW() - INTERVAL 7 DAY)
    GROUP BY table_name
) t2 ON t2.table_name = t1.table_name
WHERE t1.table_date >= DATE(NOW() - INTERVAL 7 DAY)
GROUP BY t2.table_name, t2.AVG_XDR, t2.Threshold;

动态日期透视（自动适配过去7天日期） #

若需自动识别过去7天的所有日期并生成对应列，可使用动态SQL拼接（以MySQL为例）：

SET @sql = NULL;

-- 拼接日期列的CASE语句
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN table_date = ''',
            table_date,
            ''' THEN xdr_count END) AS `',
            table_date,
            '`'
        )
    ) INTO @sql
FROM stat
WHERE table_date >= DATE(NOW() - INTERVAL 7 DAY);

-- 组装完整查询语句
SET @sql = CONCAT(
    'SELECT t2.table_name, t2.AVG_XDR AS `Avg(XDR)`, t2.Threshold, ', @sql, ' 
    FROM stat t1
    INNER JOIN (
        SELECT table_name, AVG(xdr_count) AS AVG_XDR, AVG(xdr_count)*0.8 AS Threshold
        FROM stat
        WHERE table_date >= DATE(NOW() - INTERVAL 7 DAY)
        GROUP BY table_name
    ) t2 ON t2.table_name = t1.table_name
    WHERE t1.table_date >= DATE(NOW() - INTERVAL 7 DAY)
    GROUP BY t2.table_name, t2.AVG_XDR, t2.Threshold'
);

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

关键说明 #

• 静态透视写法简洁，适合日期范围固定的场景，但日期变化时需手动修改列定义；
• 动态透视会自动读取过去7天的所有日期并生成对应列，无需手动维护列名，适配性更强；
• 两种写法均将每个table_name的平均XDR值、阈值，以及过去7天每日的xdr_count横向展示，完全匹配透视表需求。

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