第05次上机课参考答案--单表查询之聚合汇总统计查询

–单表查询之聚合汇总统计查询

–还原studentdb
–使用studentdb

–采用"学生课程成绩"表做演示
–构造实验用表和数据，一共10000行，7列
if exists(select * from sysobjects where name=‘学生课程成绩’)
drop table 学生课程成绩
go
select top 10000 学生.学号,姓名,性别,课程.课程代码,课程名称,平时成绩,考试成绩
into 学生课程成绩
from 学生
inner join 成绩 on 学生.学号=成绩.学号
inner join 课程 on 成绩.课程代码=课程.课程代码
go
–01查看学号为 2002014107 , 2002014108 两位学生的学习情况
–分别查看
select * from 学生课程成绩 where 学号=‘2002014107’
select * from 学生课程成绩 where 学号=‘2002014108’
–或者：整体查看，用逻辑复合方式完成
select * from 学生课程成绩 where 学号=‘2002014107’ or 学号=‘2002014108’
–或者：整体查看，用集合包含方式完成
select * from 学生课程成绩 where 学号 in (‘2002014107’,‘2002014108’)
–或者 并集方式来组织最终结果
select * from 学生课程成绩 where 学号=‘2002014107’
union
select * from 学生课程成绩 where 学号=‘2002014108’

–02统计学号为2002014107,2002014108两位学生的选课门数，平时成绩总分，平时成绩平均分
–选课门数即行计数的结果，一个学号对应多门课程
select * from 学生课程成绩

select count() as 选课门数 from 学生课程成绩 where 学号=‘2002014107’
select count() as 选课门数 from 学生课程成绩 where 学号=‘2002014108’
–思路:总分即求和用sum函数,均分用avg函数完成
select
count(*) as 选课门数
,sum(平时成绩) as 平时成绩总分
,avg(平时成绩) as 平时成绩均分
from 学生课程成绩
where 学号=‘2002014107’
–思考
– 1为何avg<>(sum/count)
– 2精度丢失的原因是什么？
– 3如何保留精度呢？
select convert(decimal(5,2),avg(convert(decimal(3,1),平时成绩))) as 精确平时成绩 from 学生课程成绩 where 学号=‘2002014107’
select 80.83 * 12
select convert(decimal(10,7),avg(convert(decimal(3,1),平时成绩))) as 精确平时成绩 from 学生课程成绩 where 学号=‘2002014107’
select 80.83333333 * 12
– 4如果不转变数据类型，这个精度是如何得到的？是用的什么取整方式得到的那个整数呢？
– 能不能自己设计一个实验来验证一下？
select convert(int,convert(decimal(5,2),59.00)) as 系统取整结果
select convert(int,convert(decimal(5,2),59.01)) as 系统取整结果
select convert(int,convert(decimal(5,2),59.49)) as 系统取整结果
select convert(int,convert(decimal(5,2),59.50)) as 系统取整结果
select convert(int,convert(decimal(5,2),59.51)) as 系统取整结果
select convert(int,convert(decimal(5,2),59.60)) as 系统取整结果
select convert(int,convert(decimal(5,2),59.99)) as 系统取整结果
– 说明系统是向下取整
– 取整函数应用
– ceiling,floor,random
–【四舍五入取整截取】
select round(54.56,0)
–【向下取整截取】
SELECT FLOOR(54.56)
–【向上取整截取】
SELECT CEILING(13.15)

–03查看课程名称为C语言，DSP器件及其应用两门课程的选课成绩信息
–从简单，单条件入手
select *
from 学生课程成绩
where 课程名称 = ‘C语言’
–思考原因，为何没有数据，是因为条件太严格了
select *
from 学生课程成绩
where 课程名称 like ‘%C语言%’
– 再加一个条件
select *
from 学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
– 为了方便查看，按课程名称排序
select *
from 学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
order by 课程名称

–04统计课程名称为C语言，DSP器件及其应用两门课程的选课人数，平时成绩总分，平时成绩平均分
–从简单入手，一门一门的统计
select count(*) as C语言选课人数
from 学生课程成绩
where 课程名称 like ‘%C语言%’

select count(*) as DSP器件及其应用选课人数
from 学生课程成绩
where 课程名称 =‘DSP器件及其应用’
–换成 sum(平时成绩) 就是某课程的平时成绩总分
select sum(平时成绩) as C语言平时成绩总分
from 学生课程成绩
where 课程名称 like ‘%C语言%’

select sum(平时成绩) as DSP器件及其应用平时成绩总分
from 学生课程成绩
where 课程名称 =‘DSP器件及其应用’
–换成 avg(平时成绩) 就是某课程的平时成绩平均分
select avg(平时成绩) as C语言平时成绩均分
from 学生课程成绩
where 课程名称 like ‘%C语言%’

select avg(平时成绩) as DSP器件及其应用平时成绩均分
from 学生课程成绩
where 课程名称 =‘DSP器件及其应用’

–能不能一次全都查出来呢？
select
count(*) as C语言选课人数
,sum(平时成绩) as C语言平时成绩总分
,avg(平时成绩) as C语言平时成绩均分
from 学生课程成绩
where 课程名称 like ‘%C语言%’

select
count() as DSP器件及其应用选课人数
,sum(平时成绩) as DSP器件及其应用平时成绩总分
,avg(平时成绩) as DSP器件及其应用平时成绩均分
from 学生课程成绩
where 课程名称 =‘DSP器件及其应用’
–能不能更简单一些呢？
select
count() as 选课人数
,sum(平时成绩) as 平时成绩总分
,avg(平时成绩) as 平时成绩均分
from
学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
–上面这个结果显然不对，它不是用户或是业务需要的结果。应该把两门课程分开，那么如何分开呢？
–要分组，即按课程名称分组
select
count() as 选课人数
,sum(平时成绩) as 平时成绩总分
,avg(平时成绩) as 平时成绩均分
from
学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
group by
课程名称
–看到结果以后，请猜，上面一行是C的，还是下面一行是C的？用户在看到结果时，会不会来看你的sql源代码？
–所以，课程名称的信息应该出现在结果表中
select
课程名称
,count() as 选课人数
,sum(平时成绩) as 平时成绩总分
,avg(平时成绩) as 平时成绩均分
from
学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
group by
课程名称
–简直就是完美！
–知识点：分组统计，理解难点，组怎么分出来？分组的依据是什么？

–05将01，03的查看结果转存到别的表中，表名由用户设计创建
–分别成表和整体成表
–以下是01的查询结果
select * from 学生课程成绩 where 学号 in (‘2002014107’,‘2002014108’)
–转存到另一张表 二人成绩
select * into [二人成绩] from 学生课程成绩 where 学号 in (‘2002014107’,‘2002014108’)
–以下是03的查询结果
select *
from 学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
–转存到另一张表 二课成绩
select *
into 二课成绩
from 学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
–这就是分别成表
–那么，如何整体成表呢？
–因为01和03的查询结果集合，即结果表的表结构是一样的，所以，用union取并集即可
select * from 学生课程成绩 where 学号 in (‘2002014107’,‘2002014108’)
union
select *
from 学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
–然后再转存到：二人二课成绩
select *
into 二人二课成绩
from 学生课程成绩 where 学号 in (‘2002014107’,‘2002014108’)
union
select *
from 学生课程成绩
where
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’

–或者
select *
into 二人二课成绩2
from 学生课程成绩
where
学号 in (‘2002014107’,‘2002014108’)
or
(
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
)
–为何要用“二人二课成绩2”这个表名呢，为何要加一个"2"呢？
–如果仍然插入到“二人二课成绩”这个表中，怎么做呢？
insert into 二人二课成绩 --完成插入
select * --完成查询
from 学生课程成绩
where
学号 in (‘2002014107’,‘2002014108’)
or
(
课程名称 like ‘%C语言%’
or 课程名称 =‘DSP器件及其应用’
)
go
–查看二次插入数据后的结果
select * from 二人二课成绩
–思考，转存的作用是什么？
–思考，如果不转存到表，如何更方便的提供这些数据给用户使用。当然就是用视图啦！

–06统计所有人的选课门数
select * from 学生课程成绩
–分组，按人分组，人的标识是学号，即按学号分组
select
学号,count() as 选课门数
from
学生课程成绩
group by
学号
–如果还想看到更多的个人信息呢，如学号，姓名，性别
select
学号,姓名,性别,count() as 选课门数
from
学生课程成绩
group by
学号
–出错提示：
– 服务器: 消息 8120，级别 16，状态 1，行 1
– 列 ‘学生课程成绩.姓名’ 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
– 服务器: 消息 8120，级别 16，状态 1，行 1
– 列 ‘学生课程成绩.性别’ 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
– 改正
select
学号,姓名,性别,count() as 选课门数
from
学生课程成绩
group by
学号,姓名,性别
–再排个序
select
学号,姓名,性别,count() as 选课门数
from
学生课程成绩
group by
学号,姓名,性别
order by
4 desc

–07统计所有课程的选课人数
select * from 学生课程成绩
–分组统计，按课程分组，课程的标识是课程代码
select
课程代码,count() as 选课人数
from 学生课程成绩
group by 课程代码
–改进信息量
select
课程代码,课程名称,count() as 选课人数
from
学生课程成绩
group by
课程代码,课程名称
order by
3 desc

–08统计所有人的最高分和最低分及平均分
–这题没有说这个最高分，最低分和平均分是针对平时成绩还是考试成绩来做的统计，不妨用考试成绩
–最高，即成绩的最大值，用max
–最低，用成绩的最小值，用min
–均分，用avg即可
select
学号,姓名,性别
,max(考试成绩) as 最高分
,min(考试成绩) as 最低分
,avg(考试成绩) as 平均分
from 学生课程成绩
group by
学号,姓名,性别

–09统计所有课程的最高分，最低分及平均分
–这题没有说这个最高分，最低分和平均分是针对平时成绩还是考试成绩来做的统计，不妨用考试成绩
select
课程代码,课程名称
,max(考试成绩) as 最高分
,min(考试成绩) as 最低分
,avg(考试成绩) as 平均分
from 学生课程成绩
group by
课程代码,课程名称

–10给出平均分高于60，70，80，90分的学生的学号，姓名，性别信息
–这道题也没有说是考试成绩还是平时成绩的平均分，不妨取考试成绩作参考来完成这一题的查询。
–看题目可以分析，90分的人最少，60分的人最多，集合并起来没有意义，只有分别来查看
select
学号,姓名,性别
from
学生课程成绩
where
平均分>90 --这样写肯定没有意义，因为表里没有这样的信息列"平均分"
– 思考和改进
select
学号,姓名,性别
from
学生课程成绩
where
avg(考试成绩)>90 --这样写肯定没有意义，因为表里没有这样的信息列"平均分"
–系统会给出明确的出错提示：
– 服务器: 消息 147，级别 15，状态 1，行 6
– 聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。
–再分析：这里明显有一个聚合值：平均分的出现，而且条件是加载在这个值之上的，还有隐含的“每一个人的。。。信息”的味道。
–所以，一定是先分组，再聚合，再加载条件，因此，会用到group by …having…条件，即分组上的条件
–先把每个人的平均分查出来
select
学号,姓名,性别,avg(考试成绩) as 平均分
from
学生课程成绩
group by
学号,姓名,性别
–如果基于这个结果，做上面的查询就简单了。
–这个结果，是一个分组以后的聚合结果，重点关注：平均分这一列的数据，思考，这是由多行值聚成的一行值
–所以，对这个聚合值就可以加载条件了
select
学号,姓名,性别,avg(考试成绩) as 平均分
from
学生课程成绩
group by 学号,姓名,性别
having avg(考试成绩)>90
–这个结果对于答题而言，有多余的信息列
–改正如下
select
学号,姓名,性别 --,avg(考试成绩) as 平均分
from
学生课程成绩
group by 学号,姓名,性别
having avg(考试成绩)>90
–只需要把90换成其它对应的值，就可以完成这一题的查询了

–11给出最低分高于60，70，80，90分的课程的课程代码和课程名称
–与第10题对比，只是把针对人的统计聚合变成了针对课程
–大家自己试着写一下？！然后再参考下面的代码。
select
课程代码,课程名称
from
学生课程成绩
group by
课程代码,课程名称
having
min(考试成绩)>60 --修60为70,80,90即可。

–思考：如果查出来的结果集为空，用户应该如何读解数据和信息呢？

–12汇总每一位学生的考试成绩
–汇总会产生新的维度。
–一个人的
select 学号,姓名,性别,考试成绩
from 学生课程成绩
where 学号=‘2000013864’
order by 学号
compute sum(考试成绩)

–两个人的,多维度的汇总操作
select 学号,姓名,性别,考试成绩
from 学生课程成绩
where 学号 in (‘2000013864’,‘2001803831’)
order by 学号
compute sum(考试成绩) ,avg(考试成绩)

–两个人分别的
select 学号,姓名,性别,考试成绩
from 学生课程成绩
where 学号 in (‘2000013864’,‘2001803831’)
order by 学号
compute sum(考试成绩) by 学号

–所有人的
select 学号,姓名,性别,考试成绩
from 学生课程成绩
order by 学号
compute sum(考试成绩) by 学号
–消息提示
–“资源不足，部分结果被除去”
–原因：产生的表太多，超出了sqlserver2000的表达能力，高版本应该不存在这个问题。
–解决之道：按ctrl+T，换成用纯文本输出，就不会有这个溢出问题了。

–13汇总每一门课程的平时成绩
select 课程代码,课程名称,平时成绩
from 学生课程成绩
order by 课程代码
compute sum(平时成绩) by 课程代码

–14用create view VName as select_statement的方式，可以创建视图，思考，如果把上面查询的结果都做成视图，那么以后用户要查看数据时，有何方便？
–上面13的结果因为产生了不同维度的表，是无法做成视图的。
–但从01到11所有的结果集都是单纯的相同的维度的表,都是可以做成视图的
create view resultComputeSum
as
select top 100 percent 课程代码,课程名称,平时成绩
from 学生课程成绩
order by 课程代码
compute sum(平时成绩) by 课程代码
go
–会有语法错误
– 服务器: 消息 156，级别 15，状态 1，过程 resultComputeSum，行 6
– 在关键字 ‘compute’ 附近有语法错误。

–写一个视图的实例
if exists(select * from sysobjects where name =‘v学生课程成绩’)
drop view v学生课程成绩
go
create view v学生课程成绩
as
select top 10000 学生.学号,姓名,性别,课程.课程代码,课程名称,平时成绩,考试成绩
from 学生
inner join 成绩 on 学生.学号=成绩.学号
inner join 课程 on 成绩.课程代码=课程.课程代码
go
–查看视图
select * from v学生课程成绩
go

– 知识点：
– 1简单查询
– 2统计查询
– 3汇总操作
– 4结果转存
– 5视图生成和使用