使用 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
相关文章