计算公式VB.NET / SQL [英] Calculate formula VB.NET / SQL
问题描述
我正在使用sql server和vb.net，我有以下表格
tblSalary
名称varchar（100）
基本浮点数
DARate Float
DA varchar（50）
存储的数据如下
姓名：John
基本款：100000
DARate：30
DA :(基本* DARate）/ 100
请帮帮我，如何执行存储在DA字段中的公式（（Basic * DARate）/ 100）
谢谢
我尝试过：
计算公式vb.net / sql
i am using sql server and vb.net, I have following table
tblSalary
Name varchar(100)
Basic float
DARate Float
DA varchar(50)
data stored is as below
Name : John
Basic :100000
DARate : 30
DA :(Basic*DARate)/100
Please help me, how to execute formula ((Basic*DARate)/100) which is stored in DA field
thanks
What I have tried:
Calculate Formula vb.net / sql
推荐答案
你的表有
DA varchar(50)这是一个非常糟糕的设计。 DA 是一个数字，所以这应该是
This is a very bad design. DA is a number so this should be
DA float为什么呢？因为每次使用它时，您都必须将结果从varchar转换为float，然后转换为float。例如
Why? Because everytime you use it you are going to have to convert the results backwards and forwards from varchar to float. E.g.
UPDATE tblSalary SET DA = CAST((Basic*DARate)/100 AS VARCHAR(50))
如果您绝对坚持在表格中列DA（请参阅下面我的上一条评论），那么您可以引入TRIGGER，例如
If you absolutely insist on column DA being in the table (see my last comment below) then you could introduce a TRIGGER e.g.
CREATE TRIGGER tblSalary_AfterInsert
ON tblSalary AFTER INSERT
AS
UPDATE tblSalary
SET DA = (tblSalary.[Basic] * tblSalary.DARate)/100
FROM Inserted
WHERE tblSalary.Name = Inserted.Name注意 - 这假设您在表上有 DA 列 float 。如果您坚持将其保留为varchar，那么您需要将SET行更改为
Note - this assumes you have column DA as a float on the table. If you insist on leaving it as a varchar then you will need to change the SET line to
SET DA = CAST((Basic*DARate)/100 AS VARCHAR(50))
如果是我，我不会做任何以上的事情。 DA可以从表上的其他列计算，因此无需存储它。此外，如果[Basic]或DARate发生变化，那么也必须更新DA列 - 这是一个不必要的开销。或者你必须改变触发器以处理AfterUpdate ...它仍然是不必要的开销。
我只需要在我需要...之前进行计算数据被传递回GUI（VB应用程序）：
If it was me I wouldn't do any of the things above. DA can be calculated from other columns on the table so there is no need to store it. Furthermore if either [Basic] or DARate change then you also have to update the DA column - an unnecessary overhead. OR you have to change the trigger to also handle AfterUpdate... it's still unnecessary overhead.
I would just do the calculation when I need to ... right before the data gets passed back to the GUI (VB application):
SELECT [Name], [Basic], DARate, ([Basic]*DARate)/100 AS DA
FROM tblSalary（好吧，我的表架构会有很大的不同，但是这给你的想法）
积分以上是有效但不相关的。 OP现在已经确认varchar列包含要解析和评估的表达式。
这些文章可能有所帮助：
.NET的计算引擎 [ ^ ]
实现类似Excel的公式引擎 [ ^ ]
Jace.NET：.NET的另一个计算引擎 [ ^ ]
(Well my table schema would be very different but this gives you the idea)
Points above are valid but not relevant. OP has now confirmed that the varchar column contains an expression that is to be parsed and evaluated.
These articles may help:
A Calculation Engine for .NET[^]
Implementing an Excel-like formula engine[^]
Jace.NET: Just Another Calculation Engine for .NET[^]
选择格式（（（column1 * column2）/ 100），2）作为tablename的alis_name;
select format(((column1*column2)/100),2) as alis_name from tablename;
请参阅我对该问题的评论。
我不知道你是否能理解我关于存储公式的整个想法是多么不可接受和不好的解释。
让我解释一下最简单的解决方案。我可以根据你的三个例子给你一个例子，所以它不是最终的。事实上，你通过一些例子来解释这种情况，而不是通过数学上严格的表述来解释你理解的问题。但是我希望我的例子能解释这个想法。
让我们坐下来你只有3个选项：
1）Basic * DARate; 2）（基本+ GradePay）* DARate％3）（基本+特殊支付）* DARate。
如果你看起来不错，你会发现这不是三个，这仍然是一个。假设您在某个数据库表中有三列：Basic，DARate，GradePay和SpecialPay。
那么公式为
Please see my comments to the question.
I don't know if you can understand my explanation about how unacceptable and bad is the whole idea to store the formula.
Let me explain just the simplest possible solution. I can give you an example based on your three examples, so it's not final. The whole fact that you explain the situation by some example, but not by mathematically strict formulation, raise the concerns of your understanding. But I hope my example explains the idea.
Let's sat you have only those 3 options:
1) Basic*DARate; 2) (Basic+GradePay)*DARate% 3) (Basic+SpecialPay)*DARate.
If you look properly, you will see that this is not three, this is still one. Let's assume that you have three columns in some database table: Basic, DARate, GradePay and SpecialPay.
Then the formula is
(Basic + SpecialPay + GradePay) * DARate
对于某些客户，你只需存储GragePay = 0，并且SpecialPay = 0并获得公式＃1。
对于其他，你存储GragePay！= 0，并且SpecialPay = 0并获得公式＃2;
您存储GragePay = 0，并且SpecialPay！= 0并获得公式＃3。
此外，任何有限的那些（真正可悲的琐碎）案例都可以在一个公式中推广。无需存储。
不是那么简单吗？
另一种，非常通用的选择，是使用存储过程，完全在数据库端进行所有计算。
这篇关于计算公式VB.NET / SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
