写程序的应该都知道SQL中group by的作用,但是有时候可能会遇到无法在SQL中group by的情况,我刚好就碰到了这样的情况。于是就尝试了下在DataTable中对数据进行分组计算的方法,有两种解决办法。
1.用两层循环计算,前提条件是数据必须是要按分组的列排好序的,如下:
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("name", typeof(string)),
new DataColumn("score", typeof(int)) });
dt.Rows.Add(new object[] { "张三", 1 });
dt.Rows.Add(new object[] { "张三", 4 });
dt.Rows.Add(new object[] { "李四", 100 });
dt.Rows.Add(new object[] { "李四", 90 });
dt.Rows.Add(new object[] { "王五", 77 });
DataTable dtLwolf = dt.Clone();
for (int i = 0; i < dt.Rows.Count; )
{
DataRow dr = dtLwolf.NewRow();
string name = dt.Rows[i]["name"].ToString();
dr["name"] = name;
int score = 0;
//内层也是循环同一个表,当遇到不同的name时跳出内层循环
for (; i < dt.Rows.Count; )
{
if (name == dt.Rows[i]["name"].ToString())
{
score += Convert.ToInt32(dt.Rows[i]["score"]);
dr["score"] = score;
i++;
}
else
{
break;
}
}
dtLwolf.Rows.Add(dr);
}
dt.Columns.AddRange(new DataColumn[] { new DataColumn("name", typeof(string)),
new DataColumn("score", typeof(int)) });
dt.Rows.Add(new object[] { "张三", 1 });
dt.Rows.Add(new object[] { "张三", 4 });
dt.Rows.Add(new object[] { "李四", 100 });
dt.Rows.Add(new object[] { "李四", 90 });
dt.Rows.Add(new object[] { "王五", 77 });
DataTable dtLwolf = dt.Clone();
for (int i = 0; i < dt.Rows.Count; )
{
DataRow dr = dtLwolf.NewRow();
string name = dt.Rows[i]["name"].ToString();
dr["name"] = name;
int score = 0;
//内层也是循环同一个表,当遇到不同的name时跳出内层循环
for (; i < dt.Rows.Count; )
{
if (name == dt.Rows[i]["name"].ToString())
{
score += Convert.ToInt32(dt.Rows[i]["score"]);
dr["score"] = score;
i++;
}
else
{
break;
}
}
dtLwolf.Rows.Add(dr);
}
dtLwolf中的数据即是最终结果
2.借助DataTable的Compute方法,DataTable中数据不用事先排好序。
下面代码中的dt是跟前面的是一样的
DataTable dtName = dt.DefaultView.ToTable(true, "name");
for (int i = 0; i < dtName.Rows.Count; i++)
{
DataRow[] rows = dt.Select("name='" + dtName.Rows[i][0] + "'");
//temp用来存储筛选出来的数据
DataTable temp = dtLwolf.Clone();
foreach (DataRow row in rows)
{
temp.Rows.Add(row.ItemArray);
}
DataRow dr = dtLwolf.NewRow();
dr[0] = dtName.Rows[i][0].ToString();
dr[1] = temp.Compute("sum(score)", "");
dtLwolf.Rows.Add(dr);
}
for (int i = 0; i < dtName.Rows.Count; i++)
{
DataRow[] rows = dt.Select("name='" + dtName.Rows[i][0] + "'");
//temp用来存储筛选出来的数据
DataTable temp = dtLwolf.Clone();
foreach (DataRow row in rows)
{
temp.Rows.Add(row.ItemArray);
}
DataRow dr = dtLwolf.NewRow();
dr[0] = dtName.Rows[i][0].ToString();
dr[1] = temp.Compute("sum(score)", "");
dtLwolf.Rows.Add(dr);
}