SQL查询使用列作为公式来计算值 [英] SQL Query to use column as a formula to calculate value

问题描述

我正在处理一个请求，我需要根据另一列中指定的公式计算值

I am working on a request that i need to calculate value based on formula specified in another column

下面是我的桌子:

我需要编写查询以获取基于 FORMULA 列的值.例如我需要结果

I need to write the query to get value which will be based on FORMULA column. eg I need result as

由于公式可以是由我的列 PRICE 和 SIZE 组成的任何内容，我该如何编写查询来实现这一点?

As formula could be anything consisting of my columns PRICE and SIZE, how do i write the query to achieve this?

推荐答案

另一种相对容易实现的替代方案是使用 CLR.可以利用DataTable的Compute Method在C#中给出简单的一行代码.

Another alternative which is relatively easy to do is with a CLR. You can take advantage of the Compute Method of DataTable to give a simple one line code in C#.

[Microsoft.SqlServer.Server.SqlFunction]
public static double Evaluate(SqlString expression)
{
    return double.Parse((new DataTable()).Compute(expression.ToString(), "").ToString());
}

然后将程序集添加到 SQL Server 并创建包装函数:

Then add the assembly to SQL Server and create the wrapper function:

CREATE FUNCTION [dbo].[Evaluate](@expression [nvarchar](4000))
RETURNS [float] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [YourAssemblyName].[YourClassName].[Evaluate]
GO 

现在您可以将函数作为简单的选择语句的一部分来调用:

Now you can call the function as part of a simple select statement:

SELECT itemid, price, size, formula, 
dbo.Evaluate(REPLACE(REPLACE(formula, 'PRICE', FORMAT(price,'0.00')), 
'SIZE', FORMAT(size, '0'))) as calcvalue FROM YourTable

这篇关于SQL查询使用列作为公式来计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！