分组查询
在学习之前，我们要做一些准备工作，我们需要创建User对象和包含User对象的集合，作为后面查询和输出的数据源，参见这篇文章C#进阶之LINQ表达式总结完成准备工作。
数据源：
1、单属性分组查询全部信息
这里我们举一个最简单的例子，根据职业分组，获得每组的集合：
/* C#版本1 */IEnumerable<IGrouping<string, User>>UserGroupByOccupation=list.GroupBy(s=>s.occupation); /* C#版本2 */IEnumerable<IGrouping<string, User>>UserGroupByOccupation=fromuinlistgroupubyu.occupationintonselectn; /* C#版本3 *///这里的版本3是版本2的衍生版本，用自定义对象类ListGroupResult替代 IGrouping<string, User>classListGroupResult{ publicstringKey { get; set; } publicList<User>UserList { get; set; } } IIEnumerable<ListGroupResult>UserGroupByOccupation=fromuinlistgroupubyu.occupationintonselectnewListGroupResult() { Key=n.Key, //这个Key是occupationUserList=n.ToList() };
根据Occupation分为四组：
/* 遍历 输出 *//*适用于C#版本1 和2*/foreach(IGrouping<string, User>uinUserGroupByOccupation) { Console.WriteLine(u.Key); foreach (Useruserinu) { Console.WriteLine(PrintUserObject(user)); } } /*适用于C#版本3*/foreach(ListGroupResultuinUserGroupByOccupation) { Console.WriteLine(u.Key); foreach (Useruserinu.UserList) { Console.WriteLine(PrintUserObject(user)); } } /* 输出结果 */Teacher{id=1, name=ZhangLong, age=38, gender=True, occupation=Teacher} {id=3, name=ZhangShuai, age=38, gender=False, occupation=Teacher} Student{id=2, name=ZhangJin, age=18, gender=False, occupation=Student} {id=9, name=HuZiming, age=21, gender=True, occupation=Student} {id=10, name=HuJin, age=21, gender=False, occupation=Student} Doctor{id=4, name=LiuGuangzhi, age=38, gender=False, occupation=Doctor} {id=5, name=LiuZiming, age=38, gender=True, occupation=Doctor} {id=6, name=LiuShuai, age=29, gender=False, occupation=Doctor} Builder{id=7, name=LiuJin, age=21, gender=True, occupation=Builder} {id=8, name=JiangLong, age=38, gender=True, occupation=Builder}
2、多属性分组查询全部信息
这次，我们根据职业和性别两个属性分组，获得每组的集合：
/* C#版本1 */classListMultiGroupResult{ publicstringOccupation { get; set; } publicboolGender { get; set; } publicList<User>UserList { get; set; } } IEnumerable<ListMultiGroupResult>UserGroupByOccupationAndGender=list.GroupBy(s=>new { s.occupation, s.gender }) .Select(g=>newListMultiGroupResult() { Occupation=g.Key.occupation, Gender=g.Key.gender, UserList=g.ToList() });
这里根据Occupation和Gender分组后一共分为七组：
/* 遍历 输出 *//*适用于C#版本1*/foreach(ListMultiGroupResultuinUserGroupByOccupationAndGender) { Console.WriteLine(u.Occupation+"/"+u.Gender); foreach (Useruserinu.UserList) { Console.WriteLine(PrintUserObject(user)); } } /* 输出结果 */Teacher/True{id=1, name=ZhangLong, age=38, gender=True, occupation=Teacher} Student/False{id=2, name=ZhangJin, age=18, gender=False, occupation=Student} {id=10, name=HuJin, age=21, gender=False, occupation=Student} Teacher/False{id=3, name=ZhangShuai, age=38, gender=False, occupation=Teacher} Doctor/False{id=4, name=LiuGuangzhi, age=38, gender=False, occupation=Doctor} {id=6, name=LiuShuai, age=29, gender=False, occupation=Doctor} Doctor/True{id=5, name=LiuZiming, age=38, gender=True, occupation=Doctor} Builder/True{id=7, name=LiuJin, age=21, gender=True, occupation=Builder} {id=8, name=JiangLong, age=38, gender=True, occupation=Builder} Student/True{id=9, name=HuZiming, age=21, gender=True, occupation=Student}
3、分组并对各组进行数值计算
来看一个SQL中常用的场景例子：
/* SQL里的表达: 按照用户职业分组，查出每个分组的人数及各组的年龄最大值、最小值、平均值和总和 */SELECToccupation,COUNT(id),MAX(age),MIN(age),AVG(age),SUM(age) FROMUSERGROUPBYoccupation;
/* C#版本1 */classAgeGroupResult{ publicstringKey { get; set; } publicintMaxAge { get; set; } publicintMinAge { get; set; } publicdoubleAvgAge { get; set; } publicintSumAge { get; set; } } IEnumerable<AgeGroupResult>userList=fromuinlistgroupubyu.occupationintonselectnewAgeGroupResult() { Key=n.Key, //这个Key是occupationMaxAge=n.Max(r=>r.age), MinAge=n.Min(r=>r.age), AvgAge=n.Average(r=>r.age), SumAge=n.Sum(r=>r.age), };
/* 遍历 输出 *//*适用于C#版本1*/foreach (AgeGroupResultuinuserList) { Console.WriteLine(PrintAgeGroupObject(u)); } /* 输出结果 */{Key=Teacher, MaxAge=38, MinAge=38, AvgAge=38, SumAge=76} {Key=Student, MaxAge=21, MinAge=18, AvgAge=20, SumAge=60} {Key=Doctor, MaxAge=38, MinAge=29, AvgAge=35, SumAge=105} {Key=Builder, MaxAge=38, MinAge=21, AvgAge=29.5, SumAge=59}
