c# - 组合数据库中两个表的数据以获得总和(C#)
问题描述
我是数据库新手,需要帮助。我有一个包含两个表([tb_TotalH]、[tb_PricePerH])的数据库。第一个表 [tb_TotalH] 显示每天的总小时数,第二个表 [tb_PricePerH] 是小时价格及其创建日期。
我需要计算总量(每天的小时数乘以每小时的价格)。但诀窍是小时的价格不是固定的,它会发生变化,我需要在工作时间制定期间的价格。当我查询日期过滤器以获取标签中的价格时,我还需要获取价格。 我不知道如何开始这个! 这是我到目前为止所做的一些代码:
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True");
void DisplayData()
{
con.Open();
DataTable dt = new DataTable();
sda = new SqlDataAdapter("select * from tb_TotalH", con);
sda.Fill(dt);
dataGridView1.DataSource = dt;
con.Close();
dataGridView1.AllowUserToAddRows = false;
dataGridView1.AutoResizeColumns();
}
void BetweenDates()
{
DateTime dtFrom = Convert.ToDateTime(dtp1.Value);
DateTime dtTo = Convert.ToDateTime(dtp2.Value);
SqlDataAdapter mda = new SqlDataAdapter("select * from DailyDataEntry where Date_H between '" + dtFrom.ToShortDateString() + "' and '" + dtTo.ToShortDateString() + "' ", con);
System.Data.DataSet ds = new System.Data.DataSet();
con.Open();
mda.Fill(ds, "root");
dataGridView1.DataSource = ds.Tables["root"];
dataGridView1.Refresh();
con.Close();
}
void getPrice()
{
SqlDataReader rdr = null;
SqlCommand cmd = null;
con.Open();
string CommandText = "SELECT Price, Tax FROM tb_PricePerH WHERE Date_M = @date";
cmd = new SqlCommand(CommandText);
cmd.Connection = con;
cmd.Parameters.AddWithValue("@date", "4/22/2018");
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
lbl1.Text = Convert.ToDouble(rdr["PricePerHour"].ToString());
lbl2.Text = Convert.ToDouble(rdr["Tax"].ToString());
}
con.Close();
}
解决方案
我试图用 List 模拟你的场景。
var tablePricePerHours = new List<TablePricePerHours>
{
new TablePricePerHours{ Price = 10, Tax = 19, Date = new DateTime(2018,1,5)},
new TablePricePerHours{ Price = 10.5, Tax = 21.5, Date = new DateTime(2018,4,22)},
new TablePricePerHours{ Price = 11, Tax = 22.5, Date = new DateTime(2018,6,19)},
new TablePricePerHours{ Price = 11.5, Tax = 23, Date = new DateTime(2018,10,30)},
new TablePricePerHours{ Price = 12, Tax = 23.2, Date = new DateTime(2018,11,1)}
};
var tableTotalHours = new List<TableTotalHours>
{
new TableTotalHours{ Date = new DateTime(2018,02,15), TotalHours = 5},
new TableTotalHours{ Date = new DateTime(2018,02,19), TotalHours =10},
new TableTotalHours{ Date = new DateTime(2018,02,25), TotalHours = 8},
new TableTotalHours{ Date = new DateTime(2018,03,29), TotalHours = 7.5},
new TableTotalHours{ Date = new DateTime(2018,07,05), TotalHours = 9},
new TableTotalHours{ Date = new DateTime(2018,07,06), TotalHours = 1.5},
new TableTotalHours{ Date = new DateTime(2018,07,07), TotalHours = 12},
new TableTotalHours{ Date = new DateTime(2018,10,22), TotalHours = 10},
new TableTotalHours{ Date = new DateTime(2018,10,22), TotalHours = 10},
new TableTotalHours{ Date = new DateTime(2018,11,1), TotalHours = 8},
new TableTotalHours{ Date = new DateTime(2018,12,21), TotalHours = 8.5},
new TableTotalHours{ Date = new DateTime(2018,12,22), TotalHours = 9},
};
其中 TablePricePerHours 和 TableTotalHours 定义为
public class TableTotalHours
{
public int Id {get;set;}
public DateTime Date{get;set;}
public double TotalHours{get;set;}
}
public class TablePricePerHours
{
public int Id{get;set;}
public double Price{get;set;}
public double Tax{get;set;}
public DateTime Date{get;set;}
}
现在您可以使用 Linq 获得所需的结果
var result = tableTotalHours.Select(x=> new
{
Date=x.Date,
TotalHours=x.TotalHours,
CalculatedPrice = tablePricePerHours.Where(c=> (x.Date - c.Date).Ticks>0)
.OrderBy(c=> (x.Date - c.Date).Ticks)
.First()
.Price * x.TotalHours
});
输出
你可以在这里找到相同的
推荐阅读
- java - 如何在循环中的最后一个数字后删除多余的逗号
- python - 是否可以在函数体中使用 input() ?
- android - 当应用程序在后台时,协程流程会暂停
- javascript - Mocha + Chai + NodeJS 永远不会失败,而它应该
- c++11 - Asio UDP 从外部读取处理程序写入
- vba - VBA:如何将电子邮件复制到 Outlook 文件夹?
- python - DataFrame.drop 导致 numpy.sin 的 ufunc 循环错误
- javascript - 当键值为空时,从对象数组中删除嵌套对象
- python - TF 2.6:“AssignVariableOp”Op 的输入“资源”Op 的 float32 类型与预期的资源类型不匹配
- javascript - 从 javascript 参考案例中重现 python 中的 hmac 操作