你的位置:首页 > 数据库

[数据库]我们分组玩的游戏 还记得吗


use Myschool--------------------上机练习1---------------------查询每个年级的总学时数,并按照升序排列select gradeid as 年级,sum(Classhour) from subjectgroup by gradeidorder by SUM(Classhour)--查询每个参加考试的学员的平均分select studentno as 学号,AVG(studentresult)from resultgroup by studentnoselect * from subject--查询每门课程的平均分,并按照将序排列select subjectid as 课程,AVG(studentresult) from resultgroup by subjectidorder by AVG(studentresult) desc--查询每个学生参加的所有考试的总分,并按照降序排列select studentno as 学号,SUM(studentresult) from resultgroup by studentnoorder by SUM(studentresult) desc---------上机练习2--------------------查询每学期学时超过50的课程数use myschoolselect gradeid as 年级,COUNT(subjectid) as 课程数 from subjectwhere classhour>50group by gradeid--查询课程表的所有信息select * from subject--查询每学期学生的平均年龄select * from studentselect gradeid as 年级,AVG(DATEDIFF(yy,birthday,getdate()))as 平均年龄 from studentgroup by gradeid--查询北京地区的每学期学生人数select gradeid as 年级,COUNT(1) as 人数 from studentwhere address like('%北京%')group by gradeid--查询参加考试的学生中,平均分及格的学生记录,并按照成绩降序排列select studentno,AVG(StudentResult) as 平均分from Resultgroup by StudentNohaving AVG(StudentResult)>=60order by 平均分 desc--查询成绩表中的所有信息select * from result--查询开始日期为2014年2月22日的课程的及格平均分select subjectid,AVG(studentresult) as 平均分from Resultwhere ExamDate>='2014-2-22' and ExamDate<'2014-2-23'group by SubjectIdhaving AVG(StudentResult)>=60--统计至少有一次不及格的学生学号和次数。select studentno,COUNT(1) as 次数from Resultwhere StudentResult<60group by StudentNo


 注意:  (1)where之后不能跟聚合函数

           (2) having是对分组后的数据进行第二次筛选或者过滤,也就是说没有group by就没having

           (3)如果语句中有group by关键字,那么select后只能跟group by后出现的列,或者是聚合函数

 

SQL语句的书写顺序:                     执行顺序:

     select 列名或聚合函数              (4)投影结果

     from 表名                              (1)定位到表

     where 条件                            (2)分组前的第一道过滤

     group by 列名                        (3)分组

     having 聚合函数或者分组后的列名(5)分组后的第二道过滤

     order by                                 (6)最后排序