C＃根据sql查询计算两行的差异(C# calculate difference from two rows based on a sql query)

我有一个任务要解决。我试图在图表中显示两台机器（编号1和编号2）的操作时间。因此，我将信息存储在表格中。列是id，date，number1，number2。让我们假设我有这个特定的数据集：

id date number1 number2 1| 24.09.14 | 100 | 120 2| 01.10.14 | 150 | 160

为了显示我需要检索以下数据的信息。

((number1(2)- number1(1)) + number2(2) - number1(1))/2)/(number of days (date2 - date1))

这应该产生以下具体数字。

((150-100 + 160-120)/2)/7= 6,42

或者用简单的话说。结果应该是我所有机器的平均每日操作时间。从日期数量中提取星期六和星期日是很好的但不是必要的。

我希望你理解我的问题。本质上我面临的问题是，我不知道如何使用简单的SQL查询中的不同行。编程语言是基于剃刀的Web项目中的c＃。

I have a task to solve. I am trying to display the operation time of two machines (number1 & number 2) in a diagram. Therefore i store information in a table. The columns are id, date, number1, number2. Lets assume i have this specific dataset:

id date number1 number2 1| 24.09.14 | 100 | 120 2| 01.10.14 | 150 | 160

For displaying the information I need to retrieve the following data.

((number1(2)- number1(1)) + number2(2) - number1(1))/2)/(number of days (date2 - date1))

This should result in the following specific numbers.

((150-100 + 160-120)/2)/7= 6,42

Or in plain words. The result should be the average daily operation time from all of my machines. Substracting saturdays and sundays from the number of dates would be nice but not necessary.

I hope that you understand my question. In essence I am facing the problem that i dont know how to work with different rows from a simple sql query. The programming language is c# in a razor based web project.

最满意答案

首先，我怀疑你在数据库中只有2条记录。这里有一些代码可以计算DataSet中的每2行。

for(int i=0; i < dst.Tables[0].Rows.Count - 1; i+=2) { if(dst.Tables[0].Rows.Count % 2 != 0) Console.WriteLine("Wrong records count") int number1Row1 =Convert.ToInt32(dst.Tables[0].Rows[i]["Number1"]); int number1Row2 =Convert.ToInt32(dst.Tables[0].Rows[i]["Number2"]); int number2Row1 =Convert.ToInt32(dst.Tables[0].Rows[i+1]["Number1"]); int number2Row2 =Convert.ToInt32(dst.Tables[0].Rows[i+1]["Number2"]); DateTime dateRow1 =Convert.ToDateTime(dst.Tables[0].Rows[i]["Date"]); DateTime dateRow2 =Convert.ToDateTime(dst.Tables[0].Rows[i+1]["Date"]); double calc = ((number1Row2- number1Row1 + number2Row2 - number2Row1)/2)*(dateRow1 - dateRow2).TotalDays Console.WriteLine(calc); }

它被要求最大限度地理解。

First I doubt that you have only 2 records in database. Here some code that makes calculation for every 2 rows in DataSet.

for(int i=0; i < dst.Tables[0].Rows.Count - 1; i+=2) { if(dst.Tables[0].Rows.Count % 2 != 0) Console.WriteLine("Wrong records count") int number1Row1 =Convert.ToInt32(dst.Tables[0].Rows[i]["Number1"]); int number1Row2 =Convert.ToInt32(dst.Tables[0].Rows[i]["Number2"]); int number2Row1 =Convert.ToInt32(dst.Tables[0].Rows[i+1]["Number1"]); int number2Row2 =Convert.ToInt32(dst.Tables[0].Rows[i+1]["Number2"]); DateTime dateRow1 =Convert.ToDateTime(dst.Tables[0].Rows[i]["Date"]); DateTime dateRow2 =Convert.ToDateTime(dst.Tables[0].Rows[i+1]["Date"]); double calc = ((number1Row2- number1Row1 + number2Row2 - number2Row1)/2)*(dateRow1 - dateRow2).TotalDays Console.WriteLine(calc); }

It is wroted to be maximum clear to understand.

更多推荐