Oracle SQL如何从其他表行值提取列名并计算指定求和值？

表结构 #

table1（业务数据表） #

table2（列映射配置表） #

需求说明 #

针对table1的每条no记录，需计算三个求和值：

1. sum_out：对table2中in_out='out'对应的from_val（即table1的列名）进行求和
2. sum_cf：对table2中cf_pv='cf'对应的from_val（即table1的列名）进行求和
3. sum_out_and_cf：对table2中同时满足in_out='out'且cf_pv='cf'的from_val对应列求和

预期计算示例：

sum_out:
x1 = 2+3=5；x2=10+11=21；x3=20+21=41
sum_cf:
x1=2+4=6；x2=10+12=22；x3=20+22=42
sum_out_and_cf:
x1=2；x2=10；x3=20

解决方案 #

方法1：静态SQL（配置固定场景） #

直接根据table2的固定配置硬编码列名计算：

SELECT
    t1.no,
    t1.a + t1.b AS sum_out,
    t1.a + t1.c AS sum_cf,
    t1.a AS sum_out_and_cf
FROM table1 t1;

方法2：UNPIVOT转置关联（灵活维护场景） #

先将table1的列转成行数据，再关联table2配置做分组求和，新增列时仅需修改UNPIVOT子句：

WITH unpivoted_table1 AS (
    SELECT no, col_name, col_value
    FROM table1
    UNPIVOT (
        col_value FOR col_name IN (a, b, c)
    )
)
SELECT
    ut1.no,
    SUM(CASE WHEN t2.in_out = 'out' THEN ut1.col_value ELSE 0 END) AS sum_out,
    SUM(CASE WHEN t2.cf_pv = 'cf' THEN ut1.col_value ELSE 0 END) AS sum_cf,
    SUM(CASE WHEN t2.in_out = 'out' AND t2.cf_pv = 'cf' THEN ut1.col_value ELSE 0 END) AS sum_out_and_cf
FROM unpivoted_table1 ut1
JOIN table2 t2 ON ut1.col_name = t2.from_val
GROUP BY ut1.no;

方法3：动态SQL（配置动态变化场景） #

自动读取table2配置拼接求和逻辑，适配配置变更：

DECLARE
    v_sum_out_cols VARCHAR2(1000);
    v_sum_cf_cols VARCHAR2(1000);
    v_sum_out_cf_cols VARCHAR2(1000);
    v_sql VARCHAR2(2000);
BEGIN
    -- 拼接sum_out对应列
    SELECT LISTAGG('t1.' || from_val, ' + ') WITHIN GROUP (ORDER BY from_val)
    INTO v_sum_out_cols
    FROM table2
    WHERE in_out = 'out';

    -- 拼接sum_cf对应列
    SELECT LISTAGG('t1.' || from_val, ' + ') WITHIN GROUP (ORDER BY from_val)
    INTO v_sum_cf_cols
    FROM table2
    WHERE cf_pv = 'cf';

    -- 拼接sum_out_and_cf对应列
    SELECT LISTAGG('t1.' || from_val, ' + ') WITHIN GROUP (ORDER BY from_val)
    INTO v_sum_out_cf_cols
    FROM table2
    WHERE in_out = 'out' AND cf_pv = 'cf';

    -- 构建查询SQL
    v_sql := 'SELECT t1.no, '
             || v_sum_out_cols || ' AS sum_out, '
             || v_sum_cf_cols || ' AS sum_cf, '
             || v_sum_out_cf_cols || ' AS sum_out_and_cf '
             || 'FROM table1 t1';

    -- 执行并输出结果
    DECLARE
        CURSOR c_result IS EXECUTE IMMEDIATE v_sql;
        r_result c_result%ROWTYPE;
    BEGIN
        OPEN c_result;
        LOOP
            FETCH c_result INTO r_result;
            EXIT WHEN c_result%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('no: ' || r_result.no 
                                 || ', sum_out: ' || r_result.sum_out 
                                 || ', sum_cf: ' || r_result.sum_cf 
                                 || ', sum_out_and_cf: ' || r_result.sum_out_and_cf);
        END LOOP;
        CLOSE c_result;
    END;
END;
/

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