MSSQL中三表联合查询的实践

1. 概述

在MSSQL中，联合查询是经常使用的操作之一。它主要用于将多个表中的数据合并并按照一定的条件进行筛选和排序。三表联合查询则是在两个表的基础上再加入一个表进行查询操作。下面将介绍三表联合查询的实践。

2. 数据库结构

假设我们有三个表：学生表（Student）、课程表（Course）和成绩表（Score）。学生表中包含学生的基本信息，课程表中包含课程的基本信息，成绩表中包含学生在课程中的成绩信息。三个表的结构如下：

CREATE TABLE Student (

    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Gender VARCHAR(10)
);
CREATE TABLE Course (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Teacher VARCHAR(50)
);
CREATE TABLE Score (
    ID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    Score INT,
    FOREIGN KEY (StudentID) REFERENCES Student(ID),
    FOREIGN KEY (CourseID) REFERENCES Course(ID)
);

3. 查询操作

3.1 查询学生选课情况

我们希望查询每个学生所选的课程和成绩情况，可以使用以下查询语句：

SELECT Student.ID, Student.Name, Course.Name, Score.Score

FROM Student
JOIN Score ON Student.ID = Score.StudentID
JOIN Course ON Course.ID = Score.CourseID;

上述查询语句中，使用了多个JOIN关键字，将三个表进行了联合查询。

注意：这种查询可能会返回重复的行，因为一个学生可能选了多门课程。为了避免这种情况，我们可以使用DISTINCT关键字去重：

SELECT DISTINCT Student.ID, Student.Name, Course.Name, Score.Score

FROM Student
JOIN Score ON Student.ID = Score.StudentID
JOIN Course ON Course.ID = Score.CourseID;

3.2 查询每门课程的平均分

我们希望查询每门课程的平均分和选修该课程的学生人数，可以使用以下查询语句：

SELECT Course.Name, AVG(Score.Score) AS AverageScore, COUNT(DISTINCT Score.StudentID) AS StudentCount

FROM Course
JOIN Score ON Course.ID = Score.CourseID
GROUP BY Course.Name;

上述查询语句中，使用了GROUP BY关键字按照课程名称进行了分组，同时使用AVG函数和COUNT函数计算了平均分和学生人数。

3.3 查询每个学生的平均成绩和选课情况

我们希望查询每个学生的平均成绩和选课情况，可以使用以下查询语句：

SELECT Student.ID, Student.Name, AVG(Score.Score) AS AverageScore, 

    STUFF((SELECT ',' + Course.Name FROM Score
           JOIN Course ON Course.ID = Score.CourseID
           WHERE Score.StudentID = Student.ID
           FOR XML PATH('')), 1, 1, '') AS CourseList
FROM Student
JOIN Score ON Student.ID = Score.StudentID
GROUP BY Student.ID, Student.Name;

上述查询语句中，使用了AVG函数计算了每个学生的平均成绩。同时，使用了子查询和FOR XML PATH语句将每个学生选修的课程名拼接成一个字符串。

注意：这里使用了STUFF函数，是为了去除拼接字符串的开头逗号。详见STUFF函数文档。

4. 总结

通过以上三个查询实例，我们学习了如何在MSSQL中进行三表联合查询。在实际应用中，我们可以根据具体需要结合实际情况进行查询。在使用多个JOIN关键字进行联合查询时，需要注意避免返回重复行的情况。