DataTable分组统计：
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); }); }
//分组统计按次数排序 Dictionary<string, string> dicProjectExpectFiveParam = listProject.GroupBy(x => new { x.LHCodeID, x.ParamName }) .Where(p => !sFiveParam.Contains(p.Key.LHCodeID)) .Select(group => new { group.Key, LHCodeIDCount = group.Count() }) .OrderByDescending(t => t.LHCodeIDCount) .ToDictionary(o => o.Key.LHCodeID, p => p.Key.ParamName);
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("区域 " + " 库房" + " 数量"); 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去除重复的方法：
一、ToTable方法来自动过滤所有重复行的数据，代码如下：
DataTable dt = "您的来源dt"; DataTable dt_new = dt.DefaultView.ToTable(true, "关键列1", "关键列2");
dt_new中存储的就是我们想要的所有不重复行的数据了。
public string[] GetNamesFromDataTable(DataTable dataTable) { DataView dv = dataTable.DefaultView; dataTable = dv.ToTable(true, "Name"); string[] names = new string[dataTable.Rows.Count]; for (int i = 0; i < names.Length; i++) { names[i] = dataTable.Rows[i][0].ToString(); } return names; }
讲解：
1.DefaultView的返回类型是DataView，而DataView的定义就是：
表示用于排序、筛选、搜索、编辑和导航的System.Data.DataTable的可绑定数据的自定义视图。
所以我们在要用到对DataTable进行排序、筛选、搜索、编辑和导航操作时，就要想到用DataView.
2.public DataTable ToTable(bool distinct, params string[] columnNames)方法：
从参数名我们就可以猜出它的意思了。
distinct:表示返回的Data.DataTable是否包含所有列都具有不同值的行,默认为false。
columnNames：表示要包括在返回的System.Data.DataTable中的列名的列表。如果distinct为true,则它会根据columnNames指定的列名进行筛选过滤。
二、
namespace ConsoleApplication2 { class Program { static void Main(string[] args) { DataTable tbl = new DataTable(); tbl.Columns.Add("Id", typeof(System.Int32)); tbl.Columns.Add("City", typeof(System.String)); tbl.Columns.Add("Province", typeof(System.String)); tbl.Rows.Add(1, "武汉", "湖北"); tbl.Rows.Add(2, "应城", "湖北"); tbl.Rows.Add(3, "武汉", "湖北"); IEnumerable <DataRow> r = tbl.AsEnumerable().Distinct(new CityComparer()); //到这一步,r里就是去重复的记录了 foreach (var item in r) { Console.WriteLine(item["Id"] + "，" + item["City"] + "，" + item["Province"]); } Console.ReadLine(); } } class CityComparer : IEqualityComparer <DataRow> { public bool Equals(DataRow r1, DataRow r2) { return r1["City"] == r2["City"]; } public int GetHashCode(DataRow obj) { return obj.ToString().GetHashCode(); } } }
三、
DataTable sourceDT = new DataTable("Table1"); sourceDT.Columns.Add("Id", System.Type.GetType("System.String")); sourceDT.Columns.Add("Name", System.Type.GetType("System.String")); sourceDT.Columns.Add("Age", System.Type.GetType("System.Int32")); sourceDT.Columns.Add("Sex", System.Type.GetType("System.String")); sourceDT.Rows.Add(new object[] { "10001", "李一", 24, "男" }); sourceDT.Rows.Add(new object[] { "10001", "王二", 23, "男" }); var groups = sourceDT.AsEnumerable().GroupBy(t => t["Id"].ToString()); foreach (var group in groups) { if (group.Count() > 1) { foreach (var row in group) { sourceDT.Rows.Remove(row); } } }
List
List<Act_TypeListInfo> listFind = listAllTZ.FindAll(delegate( Act_TypeListInfo f){ return f.MainID == iMainId && f.KeyName == info.ColKeyName && f.CompID == iCompID); } );
List<string> AllList = new List<string>(); var q = from p in AllList group p by p into g select new { Key = g.Count(), BranchName = g.Key }; foreach (var v in q) Console.WriteLine("--" + v.BranchName.ToString() + "--" + v.Key.ToString());
//******* 对集合按Name属于进行分组GroupBy查询 ******** //结果中包括的字段： //1、分组的关键字：Name = g.Key //2、每个分组的数量：count = g.Count() //3、每个分组的年龄总和：ageC = g.Sum(item => item.Age) //4、每个分组的收入总和：moneyC = g.Sum(item => item.Money) //写法1：lamda 表达式写法（推荐） var ls = persons1.GroupBy(a => a.Name).Select(g => (new { name = g.Key, count = g.Count(), ageC = g.Sum(item => item.Age), moneyC = g.Sum(item => item.Money) })); //写法2：类SQL语言写法 最终编译器会把它转化为lamda表达式 var ls2 = from ps in persons1 group ps by ps.Name into g select new { name = g.Key, count = g.Count(), ageC = g.Sum(item => item.Age), moneyC = g.Sum(item => item.Money) }; dataGridView1.DataSource = ls.ToList(); //dataGridView1.DataSource = ls2.ToList();
List<TestData> list = new List<TestData>(); list.Add(new TestData() { Name = "A", Count = 3, BatNum = "001" }); list.Add(new TestData() { Name = "A", Count = 3, BatNum = "001" }); list.Add(new TestData() { Name = "A", Count = 3, BatNum = "002" }); list.Add(new TestData() { Name = "b", Count = 3, BatNum = "001" }); list.Add(new TestData() { Name = "b", Count = 6, BatNum = "002" }); list.Add(new TestData() { Name = "b", Count = 3, BatNum = "003" }); list.Add(new TestData() { Name = "b", Count = 5, BatNum = "001" }); var Query = from l in list group l by l.Name + "_" + l.BatNum into g select new { Name = g.Key.Split('_')[0].ToString(), Count = g.Sum(x=>x.Count), BatNum = g.Key.Split('_')[1].ToString() }; foreach (var v in Query) { Console.WriteLine("Name={0} Count={1} BatNum={2}",v.Name,v.Count,v.BatNum); }
var query = from l in list group l by new { l.Name, l.BatNum } into g select new { Name = g.Key.Name, Count = g.Sum(a => a.Count), BatNum = g.Key.BatNum }; string result = ""; foreach (var q in query) { result += "Name=\"" + q.Name + "\"," + "Count=\"" + q.Count + "\"," + "BatNum=\"" + q.BatNum + " \"<br />"; }
//linq多表关联分组排序统计 var temp = from ta in areaList join tb in StationList on ta.AreaID equals tb.AreaID join tc in dcMNComple on tb.MN equals tc.Key into temp1 //orderby ta.AreaID group temp1 by new { AreaName = ta.AreaName, AreaID = ta.AreaID } into temp2 select new { AreaName = temp2.Key.AreaName, AreaID = temp2.Key.AreaID, MNCount = temp2.Count(), Comple = temp2.Sum(s => s.Sum(t => t.Value)) };
List<Cart_Model> list_CartModel = new List<Cart_Model>(); /按店铺进行分组 IEnumerable<IGrouping<string, Cart_Model>> query = list_CartModel.GroupBy(pet => pet.ShopId, pet => pet); foreach (IGrouping<string, Cart_Model> info in query) { List<Cart_Model> sl = info.ToList<Cart_Model>();//分组后的集合 //也可循环得到分组后，集合中的对象，你可以用info.Key去控制 //foreach (KqiPageSetupInfo set in info) //{ //} }
//从List中查找另一个List的匹配项 var mnList = listFind.Where(d => listYQ.Select(d1 => d1.YQID).Contains(d.YQID)).ToList(); //或 (FindAll) var mnList = listFind.Where(delegate(T_MNYQInfo f) { foreach (var itemYq in listYQ) { if (f.YQID == itemYq.YQID) { return true; } } return false; }).ToList();
//List中查找不为空的数据，去除重复 var qu = list.Where(x => x.DataTime.HasValue).Select(y => y.DataTime.Value).OrderByDescending(z => z).Distinct(); var listDT = qu.ToList();
//List中按两个值排序 OrderBy先排序 List = List.OrderBy(p => p.id).ThenBy(p => p.sid).ToList();
//List分页 DataPager1.CurrentPageIndex当前页码 PageSize分页大小 var curMNList = mnList.Skip(PageSize * (DataPager1.CurrentPageIndex - 1)).Take(PageSize).ToList();
List分组
//分组筛选 查询确定唯一数据 var sums = listCount[0].listType.GroupBy(x => new { x.MainID, x.CompID }).Select(group => new { MainID = group.Key.MainID, CompID = group.Key.CompID });
//时间分组模式 Func<DataEntity, DateTime> groupBy1 = x => x.DataTime.Date; Func<DataEntity, DateTime> groupBy2 = x => new DateTime(x.DataTime.Year, x.DataTime.Month, 1); Func<DataEntity, DateTime> groupBy3 = x => new DateTime(x.DataTime.Year, 1, 1); //如果指定了时间间隔，就用开始时间，按照特定间隔来分组 if (span > 1) { dtStart = dtStart.Date; groupBy1 = x => dtStart.AddDays(((x.DataTime.Date - dtStart).Days / span) * span); groupBy2 = x => new DateTime(dtStart.Year, dtStart.Month + ((x.DataTime.Month - dtStart.Month) / span) * span, 1); groupBy3 = x => new DateTime(dtStart.Year + ((x.DataTime.Year - dtStart.Year) / span) * span, 1, 1); } var groupByChoice = groupByType == 1 ? groupBy1 : (groupByType == 2 ? groupBy2 : groupBy3); var luTime = grMN.ToLookup(groupByChoice);
/*List对比所有项，判断 listMN1是否所有项存在于 listMN2*/ List<string> listMN1 = new List<string>(){ "399435XYX00003", "399435XYX00001", "399435XYX00002", "399435XYXKL117" }; List<string> listMN2 = new List<string>(){ "399435XYXKL117", "399435XYX00003", "399435XYX00001", "399435XYX00002", "xxxx", }; if (listMN1.All(listMN2.Contains)) //true
收集整理，非原创
2267
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
