c# - 如何从本地数据库计算百分比数字

Question

我目前正在尝试计算预算内剩余的百分比。我创建的应用程序是一个预算应用程序。我将用于计算的两列是 BudgetAmount（这是预算的当前余额）和 TransactionAmount（用于将费用和收入输入预算）。我不确定如何将 sql 查询的结果保存为十进制值，以便计算出百分比。任何帮助将不胜感激，在此先感谢您。

(TransactionType = 2) - 这意味着这种类型的交易是一笔费用

这是我认为的获取数据的两条sql语句？

SqlCommand percentage = new SqlCommand("select * from UserBudget where budgetID=@budgetid", conn);

SqlCommand percentage2 = new SqlCommand("select * from userbudgettransaction where budgetID=@budgetid AND TransactionType = 2", conn);

以下是我正在使用的数据库列：

UserBudget UserBudget UserBudgetTransaction

标签： c#sql

Answer 1

这是一个带有一些示例数据的示例：请注意，我已将除数乘以 100 以获得实际百分比。

 create table UserBudget (BudgetId int, BudgetAmount varchar(50))
 create table UserBudgetTransaction (UserBudgetTransactionId int, BudgetId int, TransactionAmount decimal(18, 2), TransactionType int)
 insert into UserBudget (BudgetId, BudgetAmount) values (1, '100000')
 insert into UserBudget (BudgetId, BudgetAmount) values (2, '200000')

 insert into UserBudgetTransaction (UserBudgetTransactionId, BudgetId, TransactionAmount, TransactionType ) values (3, 1, '1000', 1)
 insert into UserBudgetTransaction (UserBudgetTransactionId, BudgetId, TransactionAmount, TransactionType ) values (4, 1, '20000', 2)
 insert into UserBudgetTransaction (UserBudgetTransactionId, BudgetId, TransactionAmount, TransactionType) values (5, 1, '50000', 2)

 declare @budgetId int = 1

 select * from UserBudget
 select * from UserBudgetTransaction

 SELECT (SUM(TransactionAmount) / BudgetAmount) * 100 as Percentage 
 FROM UserBudget JOIN UserBudgetTransaction ON UserBudget.BudgetID = UserBudgetTransaction.BudgetID 
 WHERE UserBudget.BudgetID = @budgetid AND TransactionType = 2 
 group by UserBudget.BudgetID, BudgetAmount

这将返回这些值：

在代码中：

var sql = @"SELECT (SUM(TransactionAmount) / BudgetAmount) * 100 as Percentage 
 FROM UserBudget JOIN UserBudgetTransaction ON UserBudget.BudgetID = UserBudgetTransaction.BudgetID
 WHERE UserBudget.BudgetID = @budgetid AND TransactionType = 2
 group by UserBudget.BudgetID, BudgetAmount";

 decimal result;

 using (var conn = new SqlConnection("Server=localhost;Database=Budgets;Trusted_Connection=True"))
 {
    using (var cmd = new SqlCommand(sql, conn))
    {
        cmd.CommandType = CommandType.Text;
        var parameter = cmd.Parameters.Add("budgetId", SqlDbType.Int);
        parameter.Value = 1;
        conn.Open();

        result = (decimal)cmd.ExecuteScalar();      
    }
 }

 Console.WriteLine(result);

返回：