CH06_联接查询和分组查询

CH06_联接查询和分组查询

本章目标

• 掌握GROUP BY分组查询
• 掌握多表联接查询

数据分组查询

###GROUP BY关键字

• GROUP BY是SQL中的一个子句，用于根据一个或多个列对结果集进行分组。它通常与聚合函数（如COUNT、SUM、AVG等）一起使用，以便对每个组进行计算。通过GROUP BY子句，可以将具有相似值的行分组在一起，并对每个组执行聚合计算，从而对数据进行汇总和分析。

• 使用GROUP BY子句时，需要指定要分组的列。可以按照一个或多个列进行分组，以便将数据分成不同的组。在每个组内，可以对列进行聚合计算，以获得每个组的汇总结果。

• GROUP BY子句在数据分析和报表生成中非常有用，因为它允许用户对数据进行分组和汇总，以便更好地理解数据和提取有价值的信息。

GROUP BY语法如下：

SELECT column1, column2, ..., aggregate_function(column)  
FROM table_name  
GROUP BY column1, column2, ...;

案例1：根据成绩表的科目分组，计算学员每科目的平均分

SELECT SubjectId as 学科,AVG(StudentResult) as 平均分  
FROM Result
GROUP BY SubjectId

案例2：根据成绩表的科目分组，计算学员每科目的平均分，并根据平均分逆序排序

SELECT SubjectId as 学科,AVG(StudentResult) as 平均分
FROM Result
GROUP BY SubjectId
ORDER BY AVG(StudentResult) DESC

多表联接查询

​多表联接查询是数据库查询的一种，它允许用户在查询中组合来自两个或更多表的数据。联接查询基于两个或多个表之间的某些列的相似性，将它们的数据组合在一起。

以下是一些常见的多表联接查询的示例：

• 内联接（INNER JOIN）：返回两个表中具有匹配值的行。

SELECT *  
FROM table1  
INNER JOIN table2  
ON table1.column_name = table2.column_name;

案例1：查询学生表中学生学号、姓名、性别和成绩表中成绩

SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM dbo.Student AS a
INNER JOIN dbo.Result AS b ON a.StudentNo = b.StudentNo

方法2：使用WHERE关键字模拟INNER JOIN（查询结果与上面一样）

SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM Student AS a ,Result AS b
WHERE a.StudentNo = b.StudentNo

• 左联接（LEFT JOIN）：返回左表中的所有行和右表中匹配的行。如果右表中没有匹配的行，则结果为NULL。

SELECT *  
FROM table1  
LEFT JOIN table2  
ON table1.column_name = table2.column_name;

案例2：查询学员信息考试情况

SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM dbo.Student AS a
LEFT JOIN dbo.Result AS b ON a.StudentNo = b.StudentNo;

上述查询将返回一个结果集，其中包含学生表中的所有学生记录，以及与之相关的成绩表中的考试信息。如果某个学生没有参加考试记录，则对应的考试信息将显示为NULL 。

通过左连接，可以获取到左表中的所有记录，并根据连接条件将右表中的相关记录与之关联起来。这样可以方便地查询需要的信息，并且避免了因缺失数据而导致的问题。

• 右联接（RIGHT JOIN）：返回右表中的所有行和左表中匹配的行。如果左表中没有匹配的行，则结果为NULL。

SELECT *  
FROM table1  
RIGHT JOIN table2  
ON table1.column_name = table2.column_name;

案例3：查询参加考试的学员信息考试情况

SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM dbo.Student AS a
RIGHT JOIN dbo.Result AS b ON a.StudentNo = b.StudentNo;

• 全外联接（FULL OUTER JOIN）
  • 返回左表和右表中的所有行。如果某一边没有匹配的行，则结果为NULL。
  • 是一种关系型数据库中的连接操作，它能够将两个表中的所有记录进行合并，并保留两个表中的全部数据。全外连接基于关系代数的笛卡尔积操作和连接条件进行。
• 全外联接缺点
  • 结果集可能包含大量空值
  • 可能存在重复的行
  • 性能问题
  • 可能导致数据的不一致性

SELECT *  
FROM table1  
FULL OUTER JOIN table2  
ON table1.column_name = table2.column_name;

案例4：查询学生表和成绩表全部的信息记录

SELECT a.StudentNo ,StudentName,Sex,StudentResult,ExamDate
FROM Student AS a	
FULL OUTER JOIN Result AS b
ON a.StudentNo = b.StudentNo;

课后作业

1.查询前端中级班所学课程信息

2.查询考试成绩在80分以上所有的学员信息

3.查询各年纪名总课时升序排序，按年级分组

4.查询考试记录等于大于两次的所有学员信息

5.查询金蝶学员所有考试科目成绩信息

6.查询多个年纪班考试平均分，平均成绩分数低于70分的年纪信息

7.查询各年纪班的人数

8.查询各年级班学习Java相关课程数

---

参考答案