C#用DataTable实现Groupby
2011-03-07 11:33
写程序的应该都知道SQL中group by的作用,但是有时候可能会遇到无法在SQL中group by的情况,我刚好就碰到了这样的情况。于是就尝试了下在DataTable中对数据进行分组计算的方法,有两种解决办法。 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); }
dtLwolf中的数据即是最终结果 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); } |