PostgreSQL中如何将字段值作为列名动态获取对应值

原查询的问题 #

你的原查询核心错误在于join quantity q on concat('q.C_',upper(t.name)) = qv.value这一步：

• 你试图将字符串（如q.C_TV）与数值类型的qv.value做等值匹配，逻辑完全不成立；
• 直接用字符串拼接无法引用表的列名，必须先把quantity这个宽表转换为窄表结构，才能和其他表正确关联。

正确查询语句 #

针对需求（以查询tv对应记录为例），先将quantity宽表转成窄表，再关联其他维度表：

通用SQL写法（兼容多数数据库） #

SELECT 
    q.id AS "Quantity_Id",
    qv.list_of_value,
    concat('C_', upper(t.name)) AS "NAME"
FROM myTable1 t
JOIN col_entry_detail ced ON concat('C_', upper(t.name)) = ced.col_name
JOIN quantity_value qv ON qv.col_id = ced.col_id
-- 将quantity宽表拆解为(id, 列名, 列值)的窄表结构
JOIN (
    SELECT id, 'C_TV' AS col_name, C_TV AS value FROM quantity
    UNION ALL
    SELECT id, 'C_LAPTOP' AS col_name, C_LAPTOP AS value FROM quantity
    UNION ALL
    SELECT id, 'C_DESKTOP' AS col_name, C_DESKTOP AS value FROM quantity
) q ON q.col_name = concat('C_', upper(t.name)) AND q.value = qv.value
WHERE t.name = 'tv';

PostgreSQL 14+简化写法 #

SELECT 
    q.id AS "Quantity_Id",
    qv.list_of_value,
    q.col_name AS "NAME"
FROM myTable1 t
JOIN col_entry_detail ced ON concat('C_', upper(t.name)) = ced.col_name
JOIN quantity_value qv ON qv.col_id = ced.col_id
JOIN (
    SELECT id, col_name, value
    FROM quantity
    UNPIVOT (value FOR col_name IN (C_TV, C_LAPTOP, C_DESKTOP))
) q ON q.col_name = concat('C_', upper(t.name)) AND q.value = qv.value
WHERE t.name = 'tv';

预期查询结果 #

执行后会得到如下格式的结果：

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