首页 > 代码库 > datatable使用groupby进行分组统计

datatable使用groupby进行分组统计

1、用两层循环计算,前提条件是数据已经按分组的列排好序的。DataTable dt = new DataTable();dt.Columns.AddRange(new DataColumn[] { new DataColumn("name", typeof(string)),                                        new DataColumn("sex", 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 dtResult = dt.Clone();for (int i = 0; i < dt.Rows.Count; ){    DataRow dr = dtResult.NewRow();    string name = dt.Rows[i]["name"].ToString();    string sex = dt.Rows[i]["sex"].ToString();    dr["name"] = name;    dr["sex"] = sex;    int score = 0;    //内层也是循环同一个表,当遇到不同的name时跳出内层循环    for (; i < dt.Rows.Count; )    {        if (name == dt.Rows[i]["name"].ToString()&&name == dt.Rows[i]["sex"].ToString())        {            score += Convert.ToInt32(dt.Rows[i]["score"]);            dr["score"] = score;            i++;        }        else        {            break;        }    }    dtResult.Rows.Add(dr);}dtResult中的数据即是最终结果。 2、 借助DataTable的Compute方法,DataTable中数据不用事先排好序。DataTable dt = new DataTable();dt.Columns.AddRange(new DataColumn[] { new DataColumn("name", typeof(string)),                                         new DataColumn("sex", 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 dtResult = dt.Clone();DataTable dtName = dt.DefaultView.ToTable(true, "name","sex");for (int i = 0; i < dtName.Rows.Count; i++){    DataRow[] rows = dt.Select("name=‘" + dtName.Rows[i][0] + "‘ and sex=‘" + dtName.Rows[i][1] + "");    //temp用来存储筛选出来的数据    DataTable temp = dtResult.Clone();    foreach (DataRow row in rows)    {        temp.Rows.Add(row.ItemArray);    }    DataRow dr = dtResult.NewRow();    dr[0] = dtName.Rows[i][0].ToString();    dr[1] = temp.Compute("sum(score)", "");    dtResult.Rows.Add(dr);} 3、使用linq to DataTable group by实现var query = from t in dt.AsEnumerable()            group t by new { t1 = t.Field<string>("name"), t2 = t.Field<string>("sex") } into m            select new            {                name = m.Key.t1,                sex = m.Key.t2,                score = m.Sum(n => n.Field<decimal>("score"))            };if (query.ToList().Count > 0){    query.ToList().ForEach(q =>    {        Console.WriteLine(q.name + "," + q.sex + "," + q.score);    });} 

 

            DataTable dt = new DataTable("cart");            DataColumn dc1 = new DataColumn("areaid", Type.GetType("System.String"));            DataColumn dc2 = new DataColumn("house", Type.GetType("System.String"));            DataColumn dc3 = new DataColumn("seq", Type.GetType("System.String"));            DataColumn dc4 = new DataColumn("remark", Type.GetType("System.String"));            dt.Columns.Add(dc1);            dt.Columns.Add(dc2);            dt.Columns.Add(dc3);            dt.Columns.Add(dc4);            DataRow dr = dt.NewRow();            dr["areaid"] = "北京";            dr["house"] = "北京仓库";            dr["seq"] = "2";            dr["remark"] = "货到付款";            dt.Rows.Add(dr);            DataRow dr1 = dt.NewRow();            dr1["areaid"] = "北京";            dr1["house"] = "上海仓库";            dr1["seq"] = "1";            dr1["remark"] = "货到付款";            dt.Rows.Add(dr1);            DataRow dr2 = dt.NewRow();            dr2["areaid"] = "上海";            dr2["house"] = "上海仓库";            dr2["seq"] = "1";            dr2["remark"] = "货到付款";            dt.Rows.Add(dr2);            DataRow dr3 = dt.NewRow();            dr3["areaid"] = "上海";            dr3["house"] = "北京仓库";            dr3["seq"] = "1";            dr3["remark"] = "货到付款";            dt.Rows.Add(dr3);            var query = from t in dt.AsEnumerable()                        group t by new { t1 = t.Field<string>("areaid"), t2 = t.Field<string>("seq") } into m                        select new                        {                            areaid = m.Key.t1,                            seq = m.Key.t2,                            house = m.First().Field<string>("house"),                            rowcount = m.Count()                        };            Console.WriteLine("区域 " + "&nbsp; 库房" + "&nbsp;&nbsp; 数量");            foreach (var item in query.ToList())            {                if (item.rowcount > 1)                {                    MessageBox.Show(item.areaid + "---" + item.house);                }                Console.WriteLine(item.areaid + "---" + item.house + "---" + item.rowcount);                Console.WriteLine("\r\n");            }

 收集整理,非原创

 

datatable使用groupby进行分组统计