如何在SQL中计算数学公式？寻求非动态查询高效方案

针对你的需求，我们可以通过CASE表达式覆盖所有运算优先级组合来实现非动态计算，完全不需要动态SQL或循环，可直接用于视图或函数，且性能远高于你当前的方案。

核心思路是：由于你的表达式固定为val1 sign1 val2 sign2 val3 sign3 val4的结构，我们只需枚举所有运算符组合（考虑乘法优先于加减的规则），用CASE语句分别计算每种组合的结果。

最终查询代码 #

SELECT
    sid,
    val1, sign1, val2, sign2, val3, sign3, val4,
    CASE
        -- 所有运算符为乘号
        WHEN sign1 = '*' AND sign2 = '*' AND sign3 = '*' THEN val1 * val2 * val3 * val4
        -- op1=*, op2=*, op3=+/-
        WHEN sign1 = '*' AND sign2 = '*' AND sign3 = '+' THEN (val1 * val2 * val3) + val4
        WHEN sign1 = '*' AND sign2 = '*' AND sign3 = '-' THEN (val1 * val2 * val3) - val4
        -- op1=*, op2=+/- , op3=*
        WHEN sign1 = '*' AND sign2 = '+' AND sign3 = '*' THEN (val1 * val2) + (val3 * val4)
        WHEN sign1 = '*' AND sign2 = '-' AND sign3 = '*' THEN (val1 * val2) - (val3 * val4)
        -- op1=*, op2=+/- , op3=+/-
        WHEN sign1 = '*' AND sign2 = '+' AND sign3 = '+' THEN (val1 * val2) + val3 + val4
        WHEN sign1 = '*' AND sign2 = '+' AND sign3 = '-' THEN (val1 * val2) + val3 - val4
        WHEN sign1 = '*' AND sign2 = '-' AND sign3 = '+' THEN (val1 * val2) - val3 + val4
        WHEN sign1 = '*' AND sign2 = '-' AND sign3 = '-' THEN (val1 * val2) - val3 - val4
        -- op1=+/- , op2=*, op3=*
        WHEN sign1 = '+' AND sign2 = '*' AND sign3 = '*' THEN val1 + (val2 * val3 * val4)
        WHEN sign1 = '-' AND sign2 = '*' AND sign3 = '*' THEN val1 - (val2 * val3 * val4)
        -- op1=+/- , op2=*, op3=+/-
        WHEN sign1 = '+' AND sign2 = '*' AND sign3 = '+' THEN val1 + (val2 * val3) + val4
        WHEN sign1 = '+' AND sign2 = '*' AND sign3 = '-' THEN val1 + (val2 * val3) - val4
        WHEN sign1 = '-' AND sign2 = '*' AND sign3 = '+' THEN val1 - (val2 * val3) + val4
        WHEN sign1 = '-' AND sign2 = '*' AND sign3 = '-' THEN val1 - (val2 * val3) - val4
        -- op1=+/- , op2=+/- , op3=*
        WHEN sign1 = '+' AND sign2 = '+' AND sign3 = '*' THEN val1 + val2 + (val3 * val4)
        WHEN sign1 = '+' AND sign2 = '-' AND sign3 = '*' THEN val1 + val2 - (val3 * val4)
        WHEN sign1 = '-' AND sign2 = '+' AND sign3 = '*' THEN val1 - val2 + (val3 * val4)
        WHEN sign1 = '-' AND sign2 = '-' AND sign3 = '*' THEN val1 - val2 - (val3 * val4)
        -- 所有运算符为加减
        WHEN sign1 = '+' AND sign2 = '+' AND sign3 = '+' THEN val1 + val2 + val3 + val4
        WHEN sign1 = '+' AND sign2 = '+' AND sign3 = '-' THEN val1 + val2 + val3 - val4
        WHEN sign1 = '+' AND sign2 = '-' AND sign3 = '+' THEN val1 + val2 - val3 + val4
        WHEN sign1 = '+' AND sign2 = '-' AND sign3 = '-' THEN val1 + val2 - val3 - val4
        WHEN sign1 = '-' AND sign2 = '+' AND sign3 = '+' THEN val1 - val2 + val3 + val4
        WHEN sign1 = '-' AND sign2 = '+' AND sign3 = '-' THEN val1 - val2 + val3 - val4
        WHEN sign1 = '-' AND sign2 = '-' AND sign3 = '+' THEN val1 - val2 - val3 + val4
        WHEN sign1 = '-' AND sign2 = '-' AND sign3 = '-' THEN val1 - val2 - val3 - val4
        -- 未知运算符返回NULL
        ELSE NULL
    END AS Result
FROM dbo.temp_20220721;

用于视图的版本 #

如果需要在视图中使用，直接创建视图即可：

CREATE VIEW dbo.vw_temp_calculated AS
SELECT
    sid,
    val1, sign1, val2, sign2, val3, sign3, val4,
    CASE
        -- 所有运算符为乘号
        WHEN sign1 = '*' AND sign2 = '*' AND sign3 = '*' THEN val1 * val2 * val3 * val4
        -- op1=*, op2=*, op3=+/-
        WHEN sign1 = '*' AND sign2 = '*' AND sign3 = '+' THEN (val1 * val2 * val3) + val4
        WHEN sign1 = '*' AND sign2 = '*' AND sign3 = '-' THEN (val1 * val2 * val3) - val4
        -- op1=*, op2=+/- , op3=*
        WHEN sign1 = '*' AND sign2 = '+' AND sign3 = '*' THEN (val1 * val2) + (val3 * val4)
        WHEN sign1 = '*' AND sign2 = '-' AND sign3 = '*' THEN (val1 * val2) - (val3 * val4)
        -- op1=*, op2=+/- , op3=+/-
        WHEN sign1 = '*' AND sign2 = '+' AND sign3 = '+' THEN (val1 * val2) + val3 + val4
        WHEN sign1 = '*' AND sign2 = '+' AND sign3 = '-' THEN (val1 * val2) + val3 - val4
        WHEN sign1 = '*' AND sign2 = '-' AND sign3 = '+' THEN (val1 * val2) - val3 + val4
        WHEN sign1 = '*' AND sign2 = '-' AND sign3 = '-' THEN (val1 * val2) - val3 - val4
        -- op1=+/- , op2=*, op3=*
        WHEN sign1 = '+' AND sign2 = '*' AND sign3 = '*' THEN val1 + (val2 * val3 * val4)
        WHEN sign1 = '-' AND sign2 = '*' AND sign3 = '*' THEN val1 - (val2 * val3 * val4)
        -- op1=+/- , op2=*, op3=+/-
        WHEN sign1 = '+' AND sign2 = '*' AND sign3 = '+' THEN val1 + (val2 * val3) + val4
        WHEN sign1 = '+' AND sign2 = '*' AND sign3 = '-' THEN val1 + (val2 * val3) - val4
        WHEN sign1 = '-' AND sign2 = '*' AND sign3 = '+' THEN val1 - (val2 * val3) + val4
        WHEN sign1 = '-' AND sign2 = '*' AND sign3 = '-' THEN val1 - (val2 * val3) - val4
        -- op1=+/- , op2=+/- , op3=*
        WHEN sign1 = '+' AND sign2 = '+' AND sign3 = '*' THEN val1 + val2 + (val3 * val4)
        WHEN sign1 = '+' AND sign2 = '-' AND sign3 = '*' THEN val1 + val2 - (val3 * val4)
        WHEN sign1 = '-' AND sign2 = '+' AND sign3 = '*' THEN val1 - val2 + (val3 * val4)
        WHEN sign1 = '-' AND sign2 = '-' AND sign3 = '*' THEN val1 - val2 - (val3 * val4)
        -- 所有运算符为加减
        WHEN sign1 = '+' AND sign2 = '+' AND sign3 = '+' THEN val1 + val2 + val3 + val4
        WHEN sign1 = '+' AND sign2 = '+' AND sign3 = '-' THEN val1 + val2 + val3 - val4
        WHEN sign1 = '+' AND sign2 = '-' AND sign3 = '+' THEN val1 + val2 - val3 + val4
        WHEN sign1 = '+' AND sign2 = '-' AND sign3 = '-' THEN val1 + val2 - val3 - val4
        WHEN sign1 = '-' AND sign2 = '+' AND sign3 = '+' THEN val1 - val2 + val3 + val4
        WHEN sign1 = '-' AND sign2 = '+' AND sign3 = '-' THEN val1 - val2 + val3 - val4
        WHEN sign1 = '-' AND sign2 = '-' AND sign3 = '+' THEN val1 - val2 - val3 + val4
        WHEN sign1 = '-' AND sign2 = '-' AND sign3 = '-' THEN val1 - val2 - val3 - val4
        -- 未知运算符返回NULL
        ELSE NULL
    END AS Result
FROM dbo.temp_20220721;

验证结果 #

针对你的示例数据，该查询会返回：

完全符合你的期望结果。

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