PostgreSQL中无需手动输入匹配条件使用CASE表达式提取价格

问题背景 #

我有一个结构宽泛的长数据表，示例表包含IDs、A_Price、B_Price、C_Price等列。需求是提取各IDs对应的价格，将同一ID的价格以分号分隔聚合，且无需手动逐个输入ID及对应Price列名。

尝试了两种SQL写法：

• 写法1（手动指定条件，可正常运行）：

SELECT IDs, string_agg(CASE IDs WHEN 'A' THEN A_Price
                                WHEN 'B' THEN B_Price
                                WHEN 'C' THEN C_Price
                        end::text, ';') as price
FROM table
GROUP BY IDs
ORDER BY IDs

• 写法2（尝试通过CTE动态生成列名，无法识别对应列值）：

WITH CTE AS (
SELECT IDs, IDs||'_Price' as t FROM ID_list
)
SELECT IDs, string_agg(CASE IDs WHEN CTE.IDs THEN CTE.t
                        end::text, ';') as price
FROM table
LEFT JOIN CTE ON cte.IDs=table.IDs
GROUP BY IDs
ORDER BY IDs

核心问题分析 #

写法2的本质问题是：CTE中生成的t是字符串（例如'A_Price'），而非对数据表实际列的引用。SQL无法自动将字符串解析为列名，必须通过动态SQL实现这种动态列匹配逻辑。

解决方案（PostgreSQL环境） #

以下是基于PostgreSQL的实现步骤：

1. 动态生成CASE表达式分支 #

从ID_list表中获取所有ID，自动拼接成CASE语句的各个分支：

SELECT string_agg(
    format('WHEN %L THEN %I', ids, ids || '_Price'),
    ' '
) INTO case_clause
FROM ID_list;

• %L：将ID值转义为SQL字符串常量，避免语法错误。
• %I：将拼接后的列名（如'A_Price'）转义为合法的SQL标识符，防止注入风险。

2. 构造并执行完整动态SQL #

将生成的CASE分支嵌入主查询，通过EXECUTE执行动态拼接的SQL语句：

DO $$
DECLARE
    case_clause text;
BEGIN
    -- 生成CASE分支片段
    SELECT string_agg(
        format('WHEN %L THEN %I', ids, ids || '_Price'),
        ' '
    ) INTO case_clause
    FROM ID_list;

    -- 执行动态查询
    EXECUTE format(
        'SELECT IDs, string_agg(CASE IDs %s end::text, '';'') as price
         FROM table
         GROUP BY IDs
         ORDER BY IDs',
        case_clause
    );
END $$;

3. 可选：将结果存入临时表 #

如果需要保留聚合结果，可以将动态SQL的输出写入临时表：

DO $$
DECLARE
    case_clause text;
BEGIN
    SELECT string_agg(
        format('WHEN %L THEN %I', ids, ids || '_Price'),
        ' '
    ) INTO case_clause
    FROM ID_list;

    EXECUTE format(
        'CREATE TEMP TABLE aggregated_prices AS
         SELECT IDs, string_agg(CASE IDs %s end::text, '';'') as price
         FROM table
         GROUP BY IDs
         ORDER BY IDs',
        case_clause
    );
END $$;

-- 查询聚合结果
SELECT * FROM aggregated_prices;

跨数据库适配说明 #

如果使用MySQL、SQL Server等其他数据库，语法会有差异，但核心思路一致：

• MySQL：使用CONCAT拼接SQL语句，通过PREPARE和EXECUTE执行动态SQL。
• SQL Server：使用STRING_AGG生成CASE分支，通过sp_executesql执行动态SQL。

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