使用 SQL 计算/评估用户定义的公式 [英] Calculating/Evaluate user defined formula with SQL

问题描述

我有一个包含用户定义的计算公式的表和另一个包含值的表.我需要将值注入到计算公式中，才能对公式进行计算并返回结果.

I have a table that is holding user defined calculation formulas and another table that holds the values. I need to inject the value into the calculation formula to be able to evaluate the formula and return the result.

在公式表中称为计算公式的列行中的示例公式

Example formula that is in a column row called Calculation formula in the Formulas table

(310165)/(7248+7249)

这些数字代表 Values 表中对应的 ValueID

These numbers represent corresponding ValueID's in the Values table

ValueID | Value
7248    | 18521
7249    | 3835
310165  | 68546.24

我需要找到一种方法，通过与值表中的 ValueID 匹配，使用 SQL 将值注入到公式表中的公式中.

I need to find a way with SQL to inject the Value into the formula in the Formulas table by matching up with the ValueID in the Values table.

我已经查看了替换和内容以及 xml 路径，但我似乎无法找到完成此操作的方法.完成此操作后，我需要评估使用实际值生成的表达式以返回一个 int 值.任何帮助是极大的赞赏.

I have looked at replace and stuff and for xml path and I cant seem to find a way to get this done. After this is done I then need to evaluate the expression that has been produced with the actual values to return an int value. Any help is greatly appreciated.

推荐答案

此更新将动态处理整个表.注意在@Formulas 中，我添加了一个 ID 和第二个示范记录

This Update will process the entire table dynamically. Notice in @Formulas, I added an ID and a second demonstrative record

Declare @Formulas table (ID int,Calculation varchar(max))
Insert Into @Formulas values
(1,'([310165])/([7248]+[7249])'),
(2,'([999999])/([7248]+[7249])')

Declare @Values table (ValueID int,Value money)
Insert Into @Values values
(7248    , 18521),
(7249    , 3835),
(310165  , 68546.24),
(999999  , 75000)

Declare @SQL varchar(max)=''
Select  @SQL = @SQL+concat(',(',ID,',',Calculation,')') From @Formulas --Where ID=2
Select  @SQL = Replace(@SQL,'['+cast(ValueID as varchar(25))+']',Value) From @Values
Select  @SQL = 'Select * From ('+Stuff(@SQL,1,1,'values')+') N(ID,Value)'
Exec(@SQL)

退货

ID  Value
1   3.06612274109
2   3.35480407944

这篇关于使用 SQL 计算/评估用户定义的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！