请用一句sql语句取出各科的平均成绩，显示字段，科目，平均成绩

学生表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; }
        }
    }