如何根据table_b的列匹配规则计算table_a对应列的求和值？

数据表说明 #

table_a（业务数据表） #

table_b（规则配置表） #

预期输出 #

解决方案 #

由于sum_cf是存储在配置表中的动态列名列表，需要通过动态SQL实现需求，以下是主流数据库的实现示例：

MySQL 实现 #

SET @sum_cf = (SELECT sum_cf FROM table_b WHERE col = 'a');
SET @sum_pv = (SELECT sum_pv FROM table_b WHERE col = 'a');
SET @sql = CONCAT(
    'SELECT id, ', 
    REPLACE(@sum_cf, ', ', ' + '), ' AS sum_cf, ',
    @sum_pv, ' AS sum_pv ',
    'FROM table_a'
);

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

PostgreSQL 实现 #

DO $$
DECLARE
    sum_cf text;
    sum_pv text;
    sql_query text;
BEGIN
    SELECT sum_cf, sum_pv INTO sum_cf, sum_pv FROM table_b WHERE col = 'a';
    sql_query := 'SELECT id, ' || replace(sum_cf, ', ', ' + ') || ' AS sum_cf, ' || sum_pv || ' AS sum_pv FROM table_a';
    EXECUTE sql_query;
END $$;

逻辑说明 #

1. 从配置表table_b中读取目标列名列表sum_cf和PV列名sum_pv
2. 将逗号分隔的列名转换为列1 + 列2 + 列3的求和表达式
3. 拼接完整查询语句并执行动态SQL

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