如何查询junction table以获取指定课程分数最高的5名学生

首先你的现有表结构有两处需要调整，否则无法实现按单门课程查询学生分数的需求：

• 作为关联表（junction table）的University表缺少「学生对应课程分数」字段，无法获取同一门课下不同学生的成绩数据
• Courses表中的GPA字段设置错误：int(255)是非法语法，且GPA属于学生选课的成绩属性，不属于课程本身的固有属性，不应该放在课程表中

修正后的关联表建表语句 #

你需要给University表加分数字段，参考语句如下：

CREATE TABLE University (
StudentID int NOT NULL,
CourseID int NOT NULL,
Score DECIMAL(4,1) NOT NULL COMMENT '学生该门课程的分数',
CONSTRAINT PK_University PRIMARY KEY
(
StudentID,
CourseID
),
FOREIGN KEY (StudentID) REFERENCES Students (StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses (CourseID)
);

查询语句示例 #

按课程ID查询 #

如果已知目标课程的ID（比如要查ID为102的课程），直接用以下语句：

SELECT 
    s.StudentID 学生ID,
    s.LastName 姓氏,
    s.FirstName 名字,
    s.StudentNum 学号,
    u.Score 课程分数
FROM 
    University u
INNER JOIN 
    Students s ON u.StudentID = s.StudentID
WHERE 
    u.CourseID = 102 -- 替换为你要查询的目标课程ID
ORDER BY 
    u.Score DESC
LIMIT 5;

按课程名称查询 #

如果需要直接用课程名称搜索，额外关联课程表即可：

SELECT 
    s.StudentID 学生ID,
    s.LastName 姓氏,
    s.FirstName 名字,
    s.StudentNum 学号,
    u.Score 课程分数
FROM 
    University u
INNER JOIN 
    Students s ON u.StudentID = s.StudentID
INNER JOIN 
    Courses c ON u.CourseID = c.CourseID
WHERE 
    c.CourseName = '计算机基础' -- 替换为你要查询的目标课程名称
ORDER BY 
    u.Score DESC
LIMIT 5;

实现逻辑说明 #

1. 通过中间关联表University拼接学生表和课程表，打通课程、学生、选课分数三者的对应关系
2. 筛选出目标课程的所有选课记录
3. 按课程分数倒序排序，取前5条结果就是该课程分数最高的5名学生

内容的提问来源于stack exchange，提问作者mike colis