基于数据库表纵向数据的运算及Result表填充SQL方案咨询

如果存在如下结构的Result表，运算公式存储在别处时，这种横向表的运算逻辑比较简单：

Result表结构 #

但当columnA、columnB、columnC的定义存储在Master表，数据值存储在Data_Source表，运算公式存储在Formula表时（各表结构如下），需要编写SQL语句填充Result表。

Master表结构 #

Formula表结构 #

Data_Source表结构 #

由于公式是动态存储的，需使用动态SQL实现计算，以下是主流数据库的具体实现：

1. MySQL/MariaDB 实现 #

先将纵向数据源转成横向结构，再结合公式计算并插入Result表：

INSERT INTO Result(id, name, columnA, columnB, columnC, result)
WITH PivotedData AS (
    SELECT 
        related_to,
        MAX(CASE WHEN column_name = 'columnA' THEN column_value END) AS columnA,
        MAX(CASE WHEN column_name = 'columnB' THEN column_value END) AS columnB,
        MAX(CASE WHEN column_name = 'columnC' THEN column_value END) AS columnC
    FROM Data_Source
    GROUP BY related_to
),
FormulaData AS (
    SELECT related_to, Formula
    FROM Formula
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY pd.related_to) AS id,
    pd.related_to AS name,
    pd.columnA,
    pd.columnB,
    pd.columnC,
    CONCAT(
        REPLACE(REPLACE(REPLACE(fd.Formula, 'columnA', pd.columnA), 'columnB', pd.columnB), 'columnC', pd.columnC),
        '=',
        CASE fd.related_to
            WHEN 'test1' THEN pd.columnA * pd.columnB / NULLIF(pd.columnC, 0)
            WHEN 'test2' THEN (pd.columnA + pd.columnB)/2
        END
    ) AS result
FROM PivotedData pd
JOIN FormulaData fd ON pd.related_to = fd.related_to;

如果公式完全动态未知，可通过拼接动态SQL执行计算：

SET @sql = '';
SELECT GROUP_CONCAT(
    CONCAT(
        'INSERT INTO Result(name, columnA, columnB, columnC, result) ',
        'SELECT ''', related_to, ''', ',
        MAX(CASE column_name WHEN 'columnA' THEN column_value END), ',',
        MAX(CASE column_name WHEN 'columnB' THEN column_value END), ',',
        MAX(CASE column_name WHEN 'columnC' THEN column_value END), ',',
        '''', REPLACE(REPLACE(REPLACE(Formula, 'columnA', MAX(CASE column_name WHEN 'columnA' THEN column_value END)), 'columnB', MAX(CASE column_name WHEN 'columnB' THEN column_value END)), 'columnC', MAX(CASE column_name WHEN 'columnC' THEN column_value END)), '=',
        CASE Formula WHEN 'columnA * columnB / columnC' THEN MAX(CASE column_name WHEN 'columnA' THEN column_value END)*MAX(CASE column_name WHEN 'columnB' THEN column_value END)/NULLIF(MAX(CASE column_name WHEN 'columnC' THEN column_value END),0) ELSE (MAX(CASE column_name WHEN 'columnA' THEN column_value END)+MAX(CASE column_name WHEN 'columnB' THEN column_value END))/2 END,
        ''';'
    )
) INTO @sql
FROM Data_Source ds
JOIN Formula f ON ds.related_to = f.related_to
GROUP BY ds.related_to;

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

2. SQL Server 实现 #

使用PIVOT转换数据结构，结合动态SQL完成插入：

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'
WITH PivotedData AS (
    SELECT 
        related_to,
        columnA, columnB, columnC
    FROM Data_Source
    PIVOT (
        MAX(column_value) FOR column_name IN (columnA, columnB, columnC)
    ) AS pvt
),
FormulaData AS (
    SELECT related_to, Formula
    FROM Formula
)
INSERT INTO Result(id, name, columnA, columnB, columnC, result)
SELECT 
    ROW_NUMBER() OVER(ORDER BY pd.related_to) AS id,
    pd.related_to AS name,
    pd.columnA,
    pd.columnB,
    pd.columnC,
    CONCAT(
        REPLACE(REPLACE(REPLACE(fd.Formula, ''columnA'', pd.columnA), ''columnB'', pd.columnB), ''columnC'', pd.columnC),
        ''='',
        CASE fd.related_to
            WHEN ''test1'' THEN pd.columnA * pd.columnB / NULLIF(pd.columnC, 0)
            WHEN ''test2'' THEN (pd.columnA + pd.columnB)/2
        END
    ) AS result
FROM PivotedData pd
JOIN FormulaData fd ON pd.related_to = fd.related_to;
';

EXEC sp_executesql @sql;

注意事项 #

• 使用NULLIF处理除数为0的异常，避免计算报错；
• 若公式类型完全未知，需通过动态SQL拼接计算逻辑，直接替换公式中的列名为对应数值后执行；
• 可根据实际业务场景调整name字段的取值逻辑。

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