Dynamic Pivot CASE WHEN SQL报#1064错误，求修正并实现期望透视结果

云原生阿岚

2026-7-30

修正Dynamic Pivot结合CASE WHEN的SQL语法错误并实现需求

原SQL的语法错误点

where子句中param_code = IN (20,43)语法错误，多余的=号，应改为param_code IN (20,43)
动态SQL拼接时，直接使用YEAR(res_date)会导致生成的SQL中保留字段名而非具体年份值，需先提取唯一年份再拼接
group by字段不完整，不符合SQL分组规则，需包含station_id, station_name, param_code三个字段
res_value字段含逗号（字符串格式），需先转换为数值类型才能进行对数、指数运算
未处理NULL值，无法实现期望输出中的-占位

修正后的SQL代码

SET @sql = NULL;

-- 提取所有唯一年份，拼接动态列语句
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        "COALESCE(CASE WHEN param_code IN (20, 43) ",
        "THEN EXP(AVG(LOG(REPLACE(res_value, ',', '') * 1))) ",
        "ELSE AVG(REPLACE(res_value, ',', '') * 1) END, '-') AS `",
        YEAR(res_date), "`"
    )
) INTO @sql
FROM wq_results;

-- 拼接完整查询语句
SET @sql = CONCAT(
    "SELECT 
        tbl1.station_id AS `Station ID`,
        tbl2.station_name AS `Station Name`,
        tbl1.param_code AS `Parameter`,
        ", @sql, "
    FROM wq_results AS tbl1
    LEFT JOIN mon_stations AS tbl2 ON tbl1.station_id = tbl2.id
    WHERE sc_id = '20' AND tbl1.param_code IN (20, 43)
    GROUP BY tbl1.station_id, tbl2.station_name, tbl1.param_code"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;