如何实现动态透视表，适配pricebucket的新增与删除？

问题场景 #

现有stkm_stockpricesuom表中，spu_pricebucket字段包含多个不同价格桶（如PRICE1-PRICE5），需要将每个价格桶对应的计算后单价转换为列，把原6列10行的表结构转换为9列2行。当前通过静态CASE语句实现了需求，但新增或删除价格桶时必须手动修改SQL，无法自动适配，需要动态生成查询的方案。

静态实现的局限性 #

你当前的静态SQL需要显式定义每个PRICE列，一旦价格桶数量变化，就得修改CASE分支，维护成本高：

SELECT spu.spu_stockcode, 
       MAX(CASE WHEN spu.spu_pricebucket='PRICE1' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE1',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE2' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE2',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE3' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE3',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE4' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE4',
       MAX(CASE WHEN spu.spu_pricebucket='PRICE5' then TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) as 'PRICE5', 
       UC.uomc_baseuomcode, 
       UOM.uom_uomdesc, 
       UC.uomc_convfactor
FROM stkm_stockpricesuom spu
left join stkm_uomconversion UC on UC.uomc_stockcode = spu.spu_stockcode
Left join stkm_stockuom UOM on UOM.UOM_UOMCODE = UC.uomc_baseuomcode
Where spu.spu_stockcode = 'VMC 100MG'
group by UOM.uom_uomdesc;

动态SQL解决方案（MySQL环境） #

可以通过动态生成CASE语句分支来自动适配价格桶的数量，以下是具体实现：

-- 1. 定义变量存储动态生成的CASE片段和完整SQL
SET @case_columns = '';
SET @sql = '';

-- 2. 查询所有唯一的pricebucket，拼接成CASE分支
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        "MAX(CASE WHEN spu.spu_pricebucket='", spu_pricebucket, "' THEN TRUNCATE(spu.spu_unitprice * UC.uomc_convfactor,2) END) AS '", spu_pricebucket, "'"
    )
) INTO @case_columns
FROM stkm_stockpricesuom
WHERE spu_stockcode = 'VMC 100MG'; -- 匹配目标商品，和原查询条件一致

-- 3. 拼接完整的SQL语句
SET @sql = CONCAT(
    "SELECT spu.spu_stockcode, ",
    @case_columns, ",
    UC.uomc_baseuomcode, 
    UOM.uom_uomdesc, 
    UC.uomc_convfactor
FROM stkm_stockpricesuom spu
LEFT JOIN stkm_uomconversion UC ON UC.uomc_stockcode = spu.spu_stockcode
LEFT JOIN stkm_stockuom UOM ON UOM.UOM_UOMCODE = UC.uomc_baseuomcode
WHERE spu.spu_stockcode = 'VMC 100MG'
GROUP BY UOM.uom_uomdesc;"
);

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

关键逻辑说明 #

• GROUP_CONCAT用于将所有pricebucket对应的CASE分支拼接成一个字符串，自动处理新增或删除的价格桶
• 动态SQL会根据当前表中实际存在的pricebucket生成对应的列，无需手动修改
• 保持了原查询的关联、过滤和分组逻辑，结果结构和静态查询一致

注意事项 #

• 如果spu_pricebucket的数量较多，需要确保group_concat_max_len参数足够大（默认是1024），可以临时调整：SET SESSION group_concat_max_len = 10000;
• 该方案针对MySQL实现，其他数据库（如SQL Server、PostgreSQL）的动态SQL语法略有不同，但核心思路一致（先获取列名列表，再拼接执行）

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