学生表A有两个字段,Id,Name,成绩表B有四个字段,Id,StudentId,Score,SubjuectType,其中StudentId对应A中Id,SubjuectType表示科目。
1.请用一句sql语句取出各科的平均成绩,显示字段,科目,平均成绩
select SubjectType,AVG(Score)as AvgScore from StuScore group by SubjectType
2.请使用一句SQL,获取平均成绩在80分以上的所有学生信息,显示字段:学生ID,姓名,平均成绩
select Students.Id,Students.Name,AVG(StuScore.Score)as AvgScore from Students join StuScore
on Students.Id=StuScore.StudentId group by Students.Id,Students.Name having AVG(StuScore.Score)>80
扩展:
在.net中实现:
class Program { static void Main(string[] args) { var students = new List<Student> { new Student { Id = "1", Name = "张三"}, new Student { Id = "2", Name = "李四"}, new Student { Id = "3", Name = "赵五"}, }; var stuscores = new List<StuScore> { new StuScore { Id = "1", StudentId = "1",Score=80.5,SubjuectType="语文"}, new StuScore { Id = "2", StudentId = "2",Score=79.5,SubjuectType="语文"}, new StuScore { Id = "3", StudentId = "3",Score=80.0,SubjuectType="语文"}, new StuScore { Id = "4", StudentId = "1",Score=79.5,SubjuectType="数学"}, new StuScore { Id = "5", StudentId = "2",Score=80.5,SubjuectType="数学"}, new StuScore { Id = "6", StudentId = "3",Score=80.3,SubjuectType="数学"}, }; //拼接表 var list = students.Join(stuscores, a => a.Id, b => b.StudentId, (a, b) => new { a, b }); foreach (var item in list) { Console.WriteLine(" 学生姓名:{0},科目:{1},分数:{2}", item.a.Name,item.b.SubjuectType,item.b.Score); } //取出各科的平均成绩,显示字段,科目,平均成绩 var avgscorelist = stuscores.GroupBy(a=>a.SubjuectType).Select(b=> new { SubjuectType= b.Key, Average = b.Average(c => c.Score) }); foreach (var item in avgscorelist) { Console.WriteLine("科目:{0},平均分:{1}", item.SubjuectType, item.Average); } //获取平均成绩在80分以上的所有学生信息,显示字段:学生ID,姓名,平均成绩 List<AvgStuScore> avgstulist = new List<AvgStuScore>(); foreach (var item in students) { AvgStuScore assmodel = new AvgStuScore(); assmodel.Id = item.Id; assmodel.Name = item.Name; var mylist=stuscores.Where(a => a.StudentId == item.Id); double totalscore = 0; foreach (var myitem in mylist) { totalscore += myitem.Score; } if(mylist.Count()!=0) { assmodel.AvgScore = totalscore / mylist.Count(); } else { assmodel.AvgScore = 0; } avgstulist.Add(assmodel); } foreach (var item in avgstulist.Where(a=>a.AvgScore>80).OrderBy(a=>a.Id)) { Console.WriteLine("学生ID:{0},姓名:{1},平均分:{2}", item.Id, item.Name,item.AvgScore); } Console.ReadKey(); } /// <summary> /// 学生表 /// </summary> public class Student { public string Id { get;set;} public string Name{ get; set;} } /// <summary> /// 学生成绩表 /// </summary> public class StuScore { public string Id { get; set; } public string StudentId { get; set; } public double Score { get; set; } public string SubjuectType { get; set; } } /// <summary> /// 学生平均成绩视图类 /// </summary> public class AvgStuScore { public string Id { get; set; } public string Name { get; set; } public double AvgScore { get; set; } } }