C#数据表中行之间的求和

Question

我试图根据特定列上的字符串对数据表中的非特定行求和。例如，字符串测试在第0行第1行上找到，在下面添加小计行，字符串测试1仅在第2行上发现，在下面增加小计行等。当然，必须有一个页脚行包含小计的总和：之前：

Col0 | Col1  | Col2 | Col3
xxxx | Test  | 12   | 21 
yyyy | Test  | 13   | 22
zzzz | Test1 | 14   | 23 
aaaa | Test2 | 15   | 24
bbbb | Test2 | 16   | 25 

我想要什么：

Col0 | Col1   | Col2 | Col3 ...Col6
xxxx | Test   | 12   | 21 
yyyy | Test   | 13   | 22
|SubTotal| 25   | 43
zzzz | Test1  | 14   | 23
|SubTotal| 14   | 23
aaaa | Test2  | 15   | 24
bbbb | Test2  | 16   | 25
|SubTotal| 31   | 49
| Total  | 70   | 115

到目前为止我所做的一切。

DataTable dTable = new DataTable();
dTable.Columns.Add("Col0", typeof(string));
dTable.Columns.Add("Col1", typeof(string));
dTable.Columns.Add("Col2", typeof(decimal));
dTable.Columns.Add("Col3", typeof(decimal));
dTable.Columns.Add("Col4", typeof(decimal));
dTable.Columns.Add("Col5", typeof(decimal));
dTable.Columns.Add("Col6", typeof(decimal));
DataTable dTable_1 = new DataTable();

string Sql_Conn = ConfigurationManager.ConnectionStrings["Ora_ConnectionString"].ConnectionString;
try
{
using (OracleConnection con = new OracleConnection(Sql_Conn))
{
using (OracleCommand Sql_Cmd = new OracleCommand(""))
{
using (OracleDataAdapter dt = new OracleDataAdapter(Sql_Cmd))
{
Sql_Cmd.CommandText = "SELECT 1 ,Col0, Col1, " +
" SUM(TEST04) Col2,  " +
" SUM(TEST03) Col3,  " +
" SUM(TEST03)+SUM(TEST04) Col4,  " +
" SUM(TEST05) Col5,  " +
" SUM(TEST03)+SUM(TEST04)+SUM(TEST05) Col6  " +
" FROM TEST  " +
" WHERE A=:A  " +
" GROUP BY Col0,Col1" +
" ORDER BY Col0,Col1";
Sql_Cmd.Connection = con;
Sql_Cmd.CommandType = CommandType.Text;
Sql_Cmd.BindByName = true;
Sql_Cmd.Parameters.Add(":A", OracleDbType.NVarchar2).Value = A;
con.Open();
using (OracleDataReader Rdr = Sql_Cmd.ExecuteReader())
{
decimal Total_Col2 = 0;
decimal Total_Col3 = 0;
decimal Total_Col4 = 0;
decimal Total_Col5 = 0;
decimal Total_Col6 = 0;
int ii = 0;
while (Rdr.Read())
{
DataRow dr = dTable.NewRow();
dr["Col0"] = Rdr.IsDBNull(1) ? "" : Rdr.GetString(1);
dr["Col1"] = Rdr.IsDBNull(2) ? "" : Rdr.GetString(2);
dr["Col2"] = Rdr.GetDecimal(3);
dr["Col3"] = Rdr.GetDecimal(4);
dr["Col4"] = Rdr.GetDecimal(5);
dr["Col5"] = Rdr.GetDecimal(6);
dr["Col6"] = Rdr.GetDecimal(7);                               
if (dTable.Rows.Count == 0)
{
Total_Col2 = Convert.ToDecimal(dr["Col2"]);
Total_Col3 = Convert.ToDecimal(dr["Col3"]);
Total_Col4 = Convert.ToDecimal(dr["Col4"]);
Total_Col5 = Convert.ToDecimal(dr["Col5"]);
Total_Col6 = Convert.ToDecimal(dr["Col6"]);
}
else if (dTable.Rows.Count > 0)
{
int current = dTable.Rows.Count;
int previous = dTable.Rows.Count - 1;
if (dr["Col1"].ToString() == dTable.Rows[previous]["Col1"].ToString())
{
Total_Col2 = Total_Col2 + Convert.ToDecimal(dTable.Rows[previous]["Col2"]);
Total_Col3 = Total_Col3 + Convert.ToDecimal(dTable.Rows[previous]["Col3"]);
Total_Col4 = Total_Col4 + Convert.ToDecimal(dTable.Rows[previous]["Col4"]);
Total_Col5 = Total_Col5 + Convert.ToDecimal(dTable.Rows[previous]["Col5"]);
Total_Col6 = Total_Col6 + Convert.ToDecimal(dTable.Rows[previous]["Col6"]);
ii = ii + 1;
}
else
{
if (dTable.Rows.Count != 1)
{
DataRow Dr_New = dTable.NewRow();
Dr_New["Col0"] = "";
Dr_New["Col1"] = "Subtotal";
Dr_New["Col2"] = Total_Col2 + Convert.ToDecimal(dTable.Rows[previous]["Col2"]); 
Dr_New["Col3"] = Total_Col3 + Convert.ToDecimal(dTable.Rows[previous]["Col3"]); 
Dr_New["Col4"] = Total_Col4 + Convert.ToDecimal(dTable.Rows[previous]["Col4"]);
Dr_New["Col5"] = Total_Col5 + Convert.ToDecimal(dTable.Rows[previous]["Col5"]);
Dr_New["Col6"] = Total_Col6 + Convert.ToDecimal(dTable.Rows[previous]["Col6"]);
dTable.Rows.Add(Dr_New);
dTable.Rows.Add();
Total_Col2 = 0;
Total_Col3 = 0;
Total_Col4 = 0;
Total_Col5 = 0;
Total_Col6 = 0;
ii = 0;
}
else if (dTable.Rows.Count == 1)
{
DataRow Dr_New = dTable.NewRow();
Dr_New["Col0"] = "-";
Dr_New["Col1"] = "Subtotal";
Dr_New["Col2"] = Total_Col2;
Dr_New["Col3"] = Total_Col3;
Dr_New["Col4"] = Total_Col4;
Dr_New["Col5"] = Total_Col5;
Dr_New["Col6"] = Total_Col6;
dTable.Rows.Add(Dr_New);
dTable.Rows.Add();
Total_Col2 = 0;
Total_Col3 = 0;
Total_Col4 = 0;
Total_Col5 = 0;
Total_Col6 = 0;
ii = 0;
}
}
}
dTable.Rows.Add(dr);
}
GridView1.DataSource = dTable;
GridView1.DataBind();
decimal total_Col2 = dTable.AsEnumerable().Sum(row => row.Field<decimal?>("Col2") ?? 0);
decimal total_Col3 = dTable.AsEnumerable().Sum(row => row.Field<decimal?>("Col3") ?? 0);
decimal total_Col4 = dTable.AsEnumerable().Sum(row => row.Field<decimal?>("Col4") ?? 0);
decimal total_Col5 = dTable.AsEnumerable().Sum(row => row.Field<decimal?>("Col5") ?? 0);
decimal total_Col6 = dTable.AsEnumerable().Sum(row => row.Field<decimal?>("Col6") ?? 0);
GridView1.FooterRow.Cells[1].Text = "Total";
GridView1.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Left;
GridView1.FooterRow.Cells[2].Text = total_Col2.ToString("N2");
GridView1.FooterRow.Cells[3].Text = total_Col3.ToString("N2");
GridView1.FooterRow.Cells[4].Text = total_Col4.ToString("N2");
GridView1.FooterRow.Cells[5].Text = total_Col5.ToString("N2");
GridView1.FooterRow.Cells[6].Text = total_Col6.ToString("N2");
}
}
}
}

上面的代码没有按照我的意愿工作，我认为这非常复杂。感谢

Answer 1

有一个ROLLUP子句，请参阅https://www.sqltutorial.org/sql-rollup/你想要类似的东西：

SELECT 
c1, c2, aggregate_function(c3)
FROM
table
GROUP BY ROLLUP (c1, c2);