2020-10-23

datatable中如何使用groupby

            int count = 8;
            string name = "小A";
            int chinese = 73, mathematics = 33;
            //创建DataSet 
            DataSet dtsAccess = new DataSet();
            dtsAccess.Tables.Add("表名");//创建表
            dtsAccess.Tables["表名"].Columns.Add("姓名", typeof(String));
            dtsAccess.Tables["表名"].Columns.Add("语文", typeof(int));
            dtsAccess.Tables["表名"].Columns.Add("数学", typeof(int));
            dtsAccess.Tables.Add("表名2");
            dtsAccess.Tables["表名2"].Columns.Add("姓名", typeof(String));
            dtsAccess.Tables["表名2"].Columns.Add("语文", typeof(int));
            dtsAccess.Tables["表名2"].Columns.Add("数学", typeof(int));
//添加数据
   for (int i = 0; count > i; i++)
    {
      if (i == 2 || i == 3)
       { 
       name = "小B";
        chinese += 6;
         mathematics += 10;
       }
      if (i == 4 || i == 5) 
       { 
       name = "小C"; 
       chinese += 4; 
       mathematics += 6; 
       }
       
         DataRow myNew = dtsAccess.Tables["表名"].NewRow();
                myNew["姓名"] = name;
                myNew["语文"] = chinese;
                myNew["数学"] = mathematics;
                dtsAccess.Tables["表名"].Rows.Add(myNew);
    }
    //分组查
    var query = from p in dtsAccess.Tables["表名"].AsEnumerable()
                        group p by new { name = p.Field<string>("姓名"), chinese = p.Field<Int32>("数学") } into m
                        select new
                        {
                          Name = m.Key.name,
                            Chinese = m.Key.chinese,
                            mathematics = m.First().Field<Int32>("语文"),
                            rowcount = m.Count()
                        };
                        // 输出
            Console.WriteLine("Linq");
            foreach (var item in query)
            {
               DataRow dr2 = dtsAccess.Tables["表名2"].NewRow();
                dr2["姓名"] = item.Name;
                dr2["语文"] = item.mathematics;
                dr2["数学"] = item.Chinese;
                dtsAccess.Tables["表名2"].Rows.Add(dr2);
              }