SQL动态将月份设为列统计月度数值总和 替代固定pivot查询方案问询

核心思路是用动态SQL替代静态PIVOT语法：先自动查询出所有存在的月份值，再动态拼接成完整的查询语句执行，新增月份后无需修改查询逻辑。

通用实现步骤 #

• 第一步：将Date字段统一格式化为AUG 21的样式，去重得到所有需要展示的月份列名
• 第二步：将列名列表拼接为聚合逻辑/PIVOT语法需要的字符串格式
• 第三步：把基础聚合逻辑、PIVOT语法和动态列名字符串拼接为完整可执行SQL，运行得到结果

注意：以下示例均默认Date字段为数据库日期类型，若你存储的是字符串格式，需要先转为日期类型再处理，比如MySQL可用STR_TO_DATE(Date, '%d/%m/%Y')转换。

不同数据库实现示例 #

MySQL #

MySQL无原生PIVOT函数，用CASE WHEN配合动态SQL实现：

SET @sql = NULL;
-- 生成所有月份的CASE WHEN聚合逻辑
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN DATE_FORMAT(Date, ''%b %y'') = ''', DATE_FORMAT(Date, '%b %y'), ''' THEN Num ELSE 0 END) AS `', DATE_FORMAT(Date, '%b %y'), '`')
) INTO @sql
FROM 你的业务表名;

-- 拼接完整查询语句
SET @full_sql = CONCAT('SELECT ''Total number for month'' AS `Name of KPI`, ', @sql, ' FROM 你的业务表名');

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

PostgreSQL #

借助crosstab函数和存储过程实现：

CREATE OR REPLACE FUNCTION get_monthly_num_total()
RETURNS TABLE ("Name of KPI" text, "AUG 21" int, "SEP 21" int) AS $$
DECLARE
    month_cols text;
    full_query text;
BEGIN
    -- 查询所有存在的月份列名
    SELECT string_agg(DISTINCT quote_ident(to_char(Date, 'Mon yy')), ', ') INTO month_cols
    FROM 你的业务表名;
    
    -- 拼接crosstab动态查询
    full_query := format('
        SELECT * FROM crosstab(
            ''SELECT ''''Total number for month'''' AS kpi_name, to_char(Date, ''''Mon yy''''), SUM(Num) 
             FROM 你的业务表名 
             GROUP BY kpi_name, to_char(Date, ''''Mon yy'')''
        ) AS ct (kpi_name text, %s)
    ', month_cols);
    
    RETURN QUERY EXECUTE full_query;
END;
$$ LANGUAGE plpgsql;

-- 调用函数直接获取结果
SELECT * FROM get_monthly_num_total();

SQL Server #

原生支持动态PIVOT语法：

DECLARE @cols AS NVARCHAR(MAX), @full_query AS NVARCHAR(MAX);

-- 获取所有月份列名
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FORMAT(Date, 'MMM yy')) 
                      FROM 你的业务表名
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 拼接PIVOT查询语句
SET @full_query = '
SELECT ''Total number for month'' AS [Name of KPI], ' + @cols + ' 
FROM (
    SELECT FORMAT(Date, ''MMM yy'') AS month, Num 
    FROM 你的业务表名
) AS src
PIVOT (
    SUM(Num) FOR month IN (' + @cols + ')
) AS pvt';

-- 执行动态查询
EXECUTE sp_executesql @full_query;

补充说明 #

• 如果需要按范围过滤数据、或者按时间顺序排序月份列，只需要修改第一步查询列名的语句即可，后续拼接逻辑无需调整
• 若需要按Regions维度拆分统计，只需要在分组逻辑里加上Regions字段即可

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