03 聚合函数

查询 Students 表中共有多少学生

直接统计。思考 Students 中，每一行都代表一个学生的信息。也就是说，只要统计出 Students 表中有多少行，就可以统计出该表中有多少学生。

这里，我们使用 COUNT(*)，它可以统计某一个表格中的行数。

 SELECT COUNT(*) AS total FROM Students

我们用 AS 来给结果一个别名 total，只是为了让结果更易读，没有什么实际的作用。

哪个同学选修的课程最多（使用聚合函数），给出学号和选课门数

思考这个过程，同时存在“学生的信息”和“选修的课程”的表，只有 StudentAssignments，也就是说，在这张表中（FROM StudentAssignments），我们需要统计出每一个学生的所有课程数。那么我们使用的是 GROUP BY 子句，它的作用是将查询的结果按照指定的一个或多个列分组。在这里，我们需要将其按学号分组，这样最终显示出来的每一行，就是一个组（即一个学号对应的所有信息）

 GROUP BY StudentID

显然，这时候我们只要统计出行数，和上一题一样：

 SELECT StudentID, COUNT(*) AS number

然后，为了只筛选出最多的一个，我们需要先将其降序排列

 ORDER BY number DESC

接着只选择第一行，这里因为我使用的是 SQL Server，需要使用 SELECT TOP 子句，如果使用的是 MySQL，就在最后一行加上 LIMIT 1; 即可。我们改造第一句：

 SELECT TOP 1 StudentID, COUNT(*) AS number

最后展示完整的代码：

 SELECT TOP 1 StudentID, COUNT(*) AS number
 FROM StudentAssignments
 GROUP BY StudentID
 ORDER BY number DESC

查询“计算机图形学”作业 3 的平均分

拆分这句话，首先我们需要作业 3 的平均分：

 SELECT AVG(Assignment3Score) AS 平均分
 FROM StudentAssignments

然后重点在于我们怎么只想要“计算机图形学”这一门课。因为只有“课程”表中有课程的名字，而又只有“选课”表中有具体的选课信息和作业 3 的分数，因此就需要先把“课程”表和“选课”表连接起来，并且只筛选出“计算机图形学”这一门课。

 JOIN Courses ON StudentAssignments.CourseID = Courses.CourseID
 WHERE Courses.CourseName = '计算机图形学';

ON 后面是连接的条件。

完整代码如下：

 SELECT AVG(Assignment3Score) AS 平均分
 FROM StudentAssignments
 JOIN Courses ON StudentAssignments.CourseID = Courses.CourseID
 WHERE Courses.CourseName = '计算机图形学';

查询选课人数最多的两门课程，给出课程号

这道题与第二道题几乎完全一致，下面只给出代码：

 SELECT TOP 2 CourseID, COUNT(*) AS 选课人数
 FROM StudentAssignments
 GROUP BY CourseID
 ORDER BY 选课人数 DESC

按性别查询男女生分别选修的平均课程数

首先还是一样的，我们要从 Students 表中筛选出男女生，并统计课程数的平均数。

 SELECT Students.Gender, AVG(课程数) AS 平均选课数
 FROM Students
 GROUP BY Students.Gender

但是这个课程数并不是普通的课程数，而是在选课表中，找到每个学生选了多少课程：

 SElECT StudentID, COUNT(*) AS 课程数
 FROM StudentAssignments
 GROUP BY StudentID

但是呢，还会存在某些学生没有选课的情况，这些选课数量实际为 0 的人也要算到学生当中。这里我们使用 LEFT JOIN 子句保证是左侧的表：

 LEFT JOIN(
     SElECT StudentID, COUNT(*) AS 课程数
     FROM StudentAssignments
     GROUP BY StudentID
 ) AS 选课统计

然后我们连接起来之后，其条件应该是 Students.StudentID = 选课统计.StudentID 两张表的信息是相同的。完整代码如下：

 SELECT Students.Gender, AVG(课程数) AS 平均选课数
 FROM Students
 LEFT JOIN(
     SElECT StudentID, COUNT(*) AS 课程数
     FROM StudentAssignments
     GROUP BY StudentID
 ) AS 选课统计 ON Students.StudentID = 选课统计.StudentID
 GROUP BY Students.Gender

查询选修三门以上（含三门）课程的学生的学号和作业 1 平均分、作业 2 平均分和作业 3 平均分

一样的：

 SELECT StudentID,
     AVG(Assignment1Score) AS 作业1平均分,
     AVG(Assignment2Score) AS 作业2平均分,
     AVG(Assignment3Score) AS 作业3平局分
 FROM StudentAssignments

然后是按照学号来分组：

 GROUP BY StudentID

最后是一个新的知识点：我们需要三门及三门以上：

 SELECT StudentID,
     AVG(Assignment1Score) AS 作业1平均分,
     AVG(Assignment2Score) AS 作业2平均分,
     AVG(Assignment3Score) AS 作业3平局分
 FROM StudentAssignments
 GROUP BY StudentID
 HAVING COUNT(*) >= 3

这道题就完成了。

查询作业 2 成绩最高的同学的学号及他选修的课程编号

首先是我们要查询的是“学号”和“课程编号”，那为了检验成绩当然是在 StudentAssignments 表中了：

 SELECT StudentID, CourseID
 FROM StudentAssignments

接着写条件：是要作业 2 成绩最高的，显然需要对作业 2 的成绩进行排序，选最高的那个人：

 WHERE StudentID = (
     SELECT TOP 1 StudentID
     FROM StudentAssignments
     ORDER BY Assignment2Score DESC
 );

完整代码：

 SELECT StudentID, CourseID
 FROM StudentAssignments
 WHERE StudentID = (
     SELECT TOP 1 StudentID
     FROM StudentAssignments
     ORDER BY Assignment2Score DESC
 );

查询每门课程作业总分（三次作业和）最高的是哪些同学，给出这些同学的学号、取得课程作业总分的课程号及作业总分

这道题就有点复杂了（其实也没有）。

首先是查询同学的学号、课程号和总分：

 SELECT StudentID, CourseID, 总分
 FROM 课程总分
 WHERE 排名 = 1

接下来我们开始处理每门课程，应当给出学号、课程号和总分

 SELECT StudentID, CourseID, 
        (Assignment1Score + Assignment2Score + Assignment3Score) AS 总分,

然后是最高的同学，显然需要排名：

 RANK() OVER(PARTITION BY CourseID ORDER BY (Assignment1Score + Assignment2Score + Assignment3Score) DESC) AS 排名

这里 OVER([PARTITION BY][ORDER BY]) 是一个固定的语法结构。其中 PARTITION BY 是对子句进行分组，ORDER BY 则是排序。这里就是按课程分组。

最终代码：

 WITH 课程总分 AS(
     SELECT StudentID, CourseID, 
        (Assignment1Score + Assignment2Score + Assignment3Score) AS 总分,
        RANK() OVER(PARTITION BY CourseID ORDER BY (Assignment1Score + Assignment2Score + Assignment3Score) DESC) AS 排名
     FROM StudentAssignments
 )
 SELECT StudentID, CourseID, 总分
 FROM 课程总分
 WHERE 排名 = 1;

查询每个同学的选课门数，如果没有选修则选课门数为0(在做题前，请先在学生表S中加入一个新同学，这个同学在 SC 表没有出现，说明他没有选修任何课程）

比较简单：

 SELECT Students.StudentID, Students.Name, COALESCE(选课数.课程数,0) AS 选课门数
 FROM Students
 LEFT JOIN(
     SELECT StudentID, COUNT(*) AS 课程数
     FROM StudentAssignments
     GROUP BY StudentID
 ) AS 选课数 ON Students.StudentID = 选课数.StudentID;