基于其他表自定义公式实现计算列：适配数据库引擎问询

Great question! Let's break this down based on your table structure and core requirement of dynamically calculating results from manually entered formulas.

First, let's align with your table structure (I'll use clearer, standardized table names for demonstration):

• formulas: Stores formula_id (primary key) and formula_text (the manually entered formula, e.g., #228 - #16 * #5)
• values_table: Stores value_id (primary key) and value (the numeric value linked to the ID)
• formula_value_mappings: Junction table linking formula_id to value_id to track which values each formula depends on

Your core ask is totally feasible—you can generate dynamic calculated results using SELECT with pre-defined formulas, but the implementation varies by database engine since it requires executing dynamic SQL. Below are practical examples for the most common engines:

1. PostgreSQL #

PostgreSQL's PL/pgSQL makes building and executing dynamic SQL straightforward. You can create a scalar function to handle the calculation:

CREATE OR REPLACE FUNCTION calculate_formula(p_formula_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_formula TEXT;
    v_result NUMERIC;
BEGIN
    -- Replace all #value_id placeholders in the formula with actual numeric values
    SELECT string_agg(REPLACE(f.formula_text, '#' || vm.value_id, v.value::TEXT), '')
    INTO v_formula
    FROM formulas f
    JOIN formula_value_mappings vm ON f.formula_id = vm.formula_id
    JOIN values_table v ON vm.value_id = v.value_id
    WHERE f.formula_id = p_formula_id
    GROUP BY f.formula_text;

    -- Execute the dynamic formula to get the result
    EXECUTE 'SELECT ' || v_formula INTO v_result;
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

To use this function and fetch calculated results for all formulas:

SELECT formula_id, formula_text, calculate_formula(formula_id) AS calculated_result
FROM formulas;

2. MySQL #

MySQL supports dynamic SQL via PREPARE/EXECUTE statements. Here's a similar function implementation:

DELIMITER //
CREATE FUNCTION calculate_formula(p_formula_id INT)
RETURNS DECIMAL(18,6)
BEGIN
    DECLARE v_formula TEXT;
    DECLARE v_result DECIMAL(18,6);
    
    -- Build the formula with value placeholders replaced
    SELECT GROUP_CONCAT(REPLACE(f.formula_text, '#' || vm.value_id, v.value) SEPARATOR '')
    INTO v_formula
    FROM formulas f
    JOIN formula_value_mappings vm ON f.formula_id = vm.formula_id
    JOIN values_table v ON vm.value_id = v.value_id
    WHERE f.formula_id = p_formula_id
    GROUP BY f.formula_text;
    
    -- Execute the dynamic calculation
    SET @sql = CONCAT('SELECT ', v_formula, ' INTO @res');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET v_result = @res;
    RETURN v_result;
END //
DELIMITER ;

Call it with:

SELECT formula_id, formula_text, calculate_formula(formula_id) AS calculated_result
FROM formulas;

3. SQL Server #

SQL Server uses sp_executesql for dynamic SQL. Since scalar functions can't directly execute dynamic SQL, we'll use a stored procedure with an output parameter:

CREATE PROCEDURE CalculateFormula
    @FormulaID INT,
    @CalculatedResult DECIMAL(18,6) OUTPUT
AS
BEGIN
    DECLARE @FormulaText NVARCHAR(MAX);
    
    -- Replace #value_id with actual values
    SELECT @FormulaText = REPLACE(f.formula_text, '#' + CAST(vm.value_id AS VARCHAR(10)), CAST(v.value AS VARCHAR(20)))
    FROM formulas f
    JOIN formula_value_mappings vm ON f.formula_id = vm.formula_id
    JOIN values_table v ON vm.value_id = v.value_id
    WHERE f.formula_id = @FormulaID
    GROUP BY f.formula_text;
    
    -- Execute the dynamic formula
    EXEC sp_executesql @FormulaText, N'@Result DECIMAL(18,6) OUTPUT', @Result = @CalculatedResult OUTPUT;
END;

To retrieve results for a specific formula:

DECLARE @Res DECIMAL(18,6);
EXEC CalculateFormula @FormulaID = 1, @CalculatedResult = @Res OUTPUT;
SELECT @Res AS CalculatedResult;

4. Oracle #

Oracle uses EXECUTE IMMEDIATE for dynamic SQL. You can create a function like this:

CREATE OR REPLACE FUNCTION calculate_formula(p_formula_id IN NUMBER)
RETURN NUMBER
IS
    v_formula VARCHAR2(1000);
    v_result NUMBER;
BEGIN
    -- Replace placeholders with values
    SELECT REPLACE(f.formula_text, '#' || vm.value_id, v.value)
    INTO v_formula
    FROM formulas f
    JOIN formula_value_mappings vm ON f.formula_id = vm.formula_id
    JOIN values_table v ON vm.value_id = v.value_id
    WHERE f.formula_id = p_formula_id
    GROUP BY f.formula_text;
    
    -- Execute the formula
    EXECUTE IMMEDIATE 'SELECT ' || v_formula INTO v_result;
    RETURN v_result;
END;
/

Call it with:

SELECT formula_id, formula_text, calculate_formula(formula_id) AS calculated_result
FROM formulas;

• SQL Injection Risk: Since formulas are manually entered, you must validate inputs to ensure they only contain numeric placeholders (#value_id), arithmetic operators, and valid syntax. Reject any formulas with unexpected SQL keywords to prevent injection attacks.
• Performance: Dynamic SQL has overhead compared to static calculations. If you need frequent calculations, consider caching results for formulas that don't change often.
• Syntax Compatibility: Ensure your manually entered formulas use syntax compatible with your target database (e.g., operator precedence, numeric type handling).

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