SQL学习笔记----第四天（SQL实战：全国高校信息及学生成绩分析统计）

本文链接：https://blog.csdn.net/weixin_38359768/article/details/112132196

SQL实战：全国高校信息及学生成绩分析统计--目录

前言

前言

前面笔记我们学习整理了很多种 SQL 查询知识，包括：子查询、多表关联、With子句、自带函数及它们的应用场景，下面我们通过两个实例进一步加深对这些知识的理解。

1.高效信息统计

在日常工作中，数据库工作者经常会遇到各种各样的数据统计需求，这些需求大部分可以通过 SQL 语句完成。当接到这样一个需求：**统计出高校数量在100以上的省份，并输出省份名称和高校数量。**看到这个需求时，首先分析需求：由于涉及到各省的高校数量，需要进行分组统计，另外输出字段中有省份名称，所以需要进行表联结操作，所以需要将高校信息表 school 与城市信息表 city 和省份信息表province 进行联结查询：

select p.name as '省份名称',	--输出省份字段  
	   count(1) as '高校数量'	--聚合函数统计省份的高校数量
	   from school s inner join city c on s.cityid=c.id	--高校信息表和城市信息表通过城市id进行表联结
	   inner join province p on c.provinceid = p.id	--城市信息表与省份信息表通过省份id表联结
	   group by p.name	--对省份名称进行分组
	   having count(1)>100	--筛选出高校数量大于100的记录
	   order by count(1) desc	--按照高校数量降序输出

结果集：
在这里插入图片描述
SQL 语句使用了三个表内联结查询，高校信息表 school 与城市信息表 city 通过城市 id 关联，城市信息表 city 与省份信息表 province 通过省份 id 关联，在通过省份名称分组后，使用 having 关键字筛选出高校数量大于 100 的省份，最后根据聚集函数 count 统计的高校数量降序输出相关字段。

再来看另一个需求：统计出全国各省份的高校总量，以“大学”命名的高校数量以及不以“大学”命名的数量。这个需求和上面的统计一样，都是以省份进行分组统计，需要高校信息表 school 与城市信息表city 和省份信息表 province 进行内联结查询：

    select  p.name as "省份名称",                                                  --输出省份字段
    count(*) as "高校总量",                                                        --聚合函数统计省份的高校数量
    sum(case  when s.name like '%大学' then 1 else 0 end ) as "以大学命名的数量" ,  --使用case条件函数统计以大学命名的高校数量
    sum(case  when s.name  like '%大学' then 0 else 1 end ) as "不以大学命名的数量" --使用case条件函数统计不以大学命名的高校数量
    from school s inner join  city  c  on s.cityid=c.id                           --高校信息表和城市信息表通过城市id进行表联结息
    inner join province  p on c.provinceid=p.id                                   --城市信息表与省份信息表通过省份id表联结
    group by  p.name

结果集：
在这里插入图片描述
查询语句中使用了 case 条件判断函数区分是否以“大学”命名的高校，将以大学命名的高校名称标记为1，否则标记为 0，最后使用 sum 聚合函数统计出总量。

2.学生成绩统计

上面通过两个高校信息的统计需求对统计需求分析及统计方法有了基本的认识，下面再看一个关于学生成绩的统计需求：**统计出每个学生的各科成绩，各科平均成绩和成绩等级。**成绩对应等级以下表为准：

成绩	等级
>90	A
80-90	B
70-80	C
60-70	D
<60	E

从上面需求分析，各科成绩从学生选课成绩表中可以直接获取，成绩等级也可以通过 case 条件判断函数来计算得到，那么如何获取各课程的平均成绩呢，对于这个指标我们可以通过 with 子句来获取：

 with  t as ( select cid,avg(grade) as avg_grade from elective  group by cid )   
 --使用with子句定义一个临时表，获取每科的平均成绩 
  select s.stu_name,                                        --输出学生姓名
 (select  cname from course c where c.cid=e.cid) as cname, --通过子查询获取课程名称
 e.grade,                                                  --输出学生课程成绩
 cast(t.avg_grade as int ) as avg_grade,                   --输出with子句查询的平均成绩
 case  when  e.grade>=90  then   'A'   
   when   (e.grade>=80 and e.grade<90  )  then 'B'
   when   (e.grade>=70 and e.grade<80  )  then 'C'
   when   (e.grade>=60 and e.grade<70  )  then 'D' 
   else  'E'
  end as "level"                                            --通过case条件函数输出成绩等级
 from 
 elective e inner join student s on e.sid=s.sid
 inner join t on t.cid= e.cid