第四章 查询和视图3

select cj.xh as 学 号,avg(cj.cj) as 平均成绩 ,max(cj.cj) as 总成绩 from sjk!cj group by cj.xh
max(cj)
avg(cj)
单表的分组查询(group by子句分组，having 子句筛选记录) 例2：基于成绩表cj(cj,kcdh,xh)，查询各门课程的 平均分，最高分，要求输出课程号、最高分。
实现方法：两表连接完后先按照学号进行分组，然后再用函数进行汇总。
max(成绩)
学号 990101 990101 990101 990102 990102 990106 990106 姓名 李林 李林 李林 高山 高山 陆海涛 陆海涛 课程代号 01 02 03 01 02 01 03 成绩 90 89 78 80 86 90 86
11
4.3.3 SELECT-SQL应用
多表的分组查询(group by子句分组，having子句 筛选记录) 首先确定连接的两表(inner join… on) 然后确定要输出的字段 接下来确定分组依据 最后确定筛选条件，如果筛选条件中没有涉及到合 计函数，则直接用where子句（ where子句在 group by子句之前）筛选，如果涉及到然后再用 having子句（ having子句紧跟group by子句 之后）进行筛选
基于成绩表cj(cj,kcdh,xh) ，查询学号前两位为”99” ，总分不低于480分的学生，要求输出字段为学号、平均成 绩、总成绩，结果按平均成绩的降序排列。
select cj.xh as 学号,avg(cj.cj) as 平均成绩 ,sum(cj.cj) as 总成绩 from sjk!cj where left(cj.xh,2)=„99‟ group by 学 号 having sum(cj.cj)>=480 order by 2/平均成绩 desc
15
4.3.3 SELECT-SQL应用
基于xs表(xh,xm,xb,zydh,xdh,jg,csrq,zp)及cj 表(cj,kcdh,xh)查询学号前两位为’99‟的，总分不低于 480分的学生，要求输出字段为学号、姓名、平均成绩、 总成绩，结果按平均成绩的降序排列。
select xs.xh as 学号,xs.xm as 姓名, avg(cj.cj) as 平均成绩,sum(cj.cj) as 总成绩 from sjk!xs inner join sjk!cj on xs.xh=cj.xh where left(xs.xh,2)='99'group by xs.xh having 总 成绩>=480 order by 3 desc
9
4.3.3 SELECT-SQL应用
单表的分组查询(group by子句分组，having子句 筛选记录) 例4：基于成绩表cj(cj,kcdh,xh) ，查询平均分不低于 80分的学生，要求输出字段为学号、平均成绩，结果按平 均成绩的降序排列。 select cj.xh as 学号,avg(cj) as 平均成绩 from sjk!cj group by 学号 having avg(cj)>=80 order by 2/平均成绩 desc
基于xs表(xh,xm,xb,zydh,xdh,jg,csrq,zp)及cj 表(cj,kcdh,xh)查看每个学生信息，输出字段为学号、 姓名、平均成绩、总成绩。
select xs.xh as 学号,xs.xm as 姓名, avg(cj.cj) as 平均成绩,sum(cj.cj) as 总成绩 from sjk!xs inner join sjk!cj on xs.xh=cj.xh group by xs.xh
4
4.3.2 SELECT-SQL格式
分组查询
确定查询中是否包含分组查询的方法---查询中的数据 是否需要借助于以下合计函数 Count(*)----统计分组后的记录数 Max()----统计分组后某个字段的最大值 Min()----统计分组后某个字段的最小值 Avg()----计算分组之后某个字段的平均值 Sum()----计算分组之后的某些字段之和
14
4.3.3 SELECT-SQL应用
基于xs表(xh,xm,xb,zydh,xdh,jg,csrq,zp)及cj 表(cj,kcdh,xh)查询总分不低于480分的学生，要求输 出字段为学号、姓名、平均成绩、总成绩，结果按平均成 绩的降序排列。
select xs.xh as 学号,xs.xm as 姓名, avg(cj.cj) as 平均成绩,sum(cj.cj) as 总成绩 from sjk!xs inner join sjk!cj on xs.xh=cj.xh group by xs.xh having 总成绩>=480 order by 3 desc
18
4.3.3 SELECT-SQL应用
6
单表的分组查询(group by子句分组，having 子句筛选记录) 例1：基于成绩表cj(cj,kcdh,xh)，查询每个学生 的平均分，最高分，要求输出学号、平均成绩、总成绩。
学号 990101 990102 990101 990102 990101 990106 990106 课程代号 01 01 03 02 02 01 03 成绩 90 80 78 86 89 90 86
5
4.3.2 SELECT-SQL格式
分组查询（先分组，再统计）
如果需要分组查询，首先确定分组的字段是什么 (group by …)，然后确定需要使用哪些分组函数 (count、max..)，最后确定分组后查询的数据筛选 条件是什么(having …)
注意：having筛选与where筛选的区别，如果 筛选条件中没有涉及到合计函数，则直接用 where子句（ where子句在group by子句之 前）筛选，如果筛选条件中涉及到然后则用 having子句（ having子句紧跟group by子 句之后）进行筛选
3
4.3.2 SELECT-SQL格式
Distinct(用于指定不允许出现重复记录) Top n / n%(显示前n条记录,或前n%条记录)
SELECT
FROM
字段1，字段2……
表1，表2…
[inner join|left join |right join|full join ] [on 连接条件 ] [WHERE 筛选条件] [GROUP BY 字段1，字段2…] [ Having 筛选条件…] [ORDER BY 字段1[ASC/DESC] ，字段2[ASC/DESC]…]
按课程号分组 组
1
组
组
2
3
分组之后按组统计
课程号 最高分 01 90 02 89 06 86
平均分 86.6 87.5 82
select cj.xh as 学 号,avg(cj.cj) as 平均成绩 ,max(cj.cj) as 总成绩 from sjk!cj group by cj.kcdh
max(cj)
学号 990101 990102 990101 990102 990101 990106 990106 课程代号 01 01 03 02 02 01 03 成绩 90 80 78 86 89 90 86
课程代号 01 01 01 02 02 03 03 学号 990101 990102 990106 990102 990101 990101 990106 成绩 90 80 90 86 89 78 86
avg(cj)
4.3.3 SELECT-SQL应用
单表的分组查询(group by子句分组，having子句筛选 记录) 例3：基于成绩表cj(cj,kcdh,xh)，查询每个学生的平均 分、最高分、最低分、考试科目数，要求输出字段为学号、平 均成绩、最高分、最低分、考试科目数。 select cj.xh as 学号,avg(cj.cj) as 平均成 绩,max(cj.cj) as 最高分,min(cj.cj) as 最低 分,count(*) as 考试科目数 from sjk!cj group by cj.xh
10
练习时间
基于成绩表cj(cj,kcdh,xh) ，查询总分不低于480 分的学生，要求输出字段为学号、平均成绩、总成绩，结 果按平均成绩的降序排列。
select cj.xh as 学号,avg(cj.cj) as 平均成绩 ,sum(cj.cj) as 总成绩 from sjk!cj group by 学号 having sum(cj.cj)>=480 order by 2/平均成绩 desc
基于教师表js、系部表xim及任课表rk，查询没有担任 课程教师的姓名和系名。
select xim.ximing,js.xm from sjk!js inner join sjk!xim on js.xdh = xim.xdh where js.gh not in (select rk.gh from sjk!rk)
16
练习时间
基于xs表(xh,xm,xb,zydh,xdh,jg,csrq,zp)及cj 表(cj,kcdh,xh)查询年龄大于20岁，平均分不低于 80分的学生，要求输出字段为学号、姓名、平均成绩、 总成绩，结果按平均成绩的降序排列。 select xs.xh as 学号,xs.xm as 姓名, avg(cj.cj) as 平均成绩,sum(cj.cj) as 总成绩 from sjk!xs inner join sjk!cj on xs.xh=cj.xh where year(date())-year(csrq)>=20 group by xs.xh having 平均成绩>=80 order by 3 desc
12
查看每个学生的学号、姓名、最高分、最低分和参加 考试门数、平均分、总分。