SQL多表查询：实现学生成绩及全班科目成绩的关联展示

Let's break down your two requirements into actionable SQL queries, using the table structures you provided.

1. Retrieve Subject_number, Student_number, Class, and Corresponding Student Grades #

To pull these fields together, we need to link the student and grades tables (since Class lives in student, while grades and identifiers are stored in grades). If you want to ensure we only include grades for subjects that exist in your subject table, we can add an extra join for validation.

Query: #

SELECT 
    g.Subject_number,
    g.Student_number,
    s.Class,
    g.Grade
FROM 
    grades g
JOIN 
    student s ON g.Student_number = s.Student_number
-- Optional: Uncomment below to filter out grades for non-existent subjects
-- JOIN 
--     subject sub ON g.Subject_number = sub.Subject_number
ORDER BY 
    g.Subject_number, s.Class, g.Student_number;

Explanation: #

• The JOIN between grades (aliased as g) and student (aliased as s) uses the shared Student_number field to connect each grade to the student's class.
• The optional join to subject ensures we don't include grades for subjects that aren't officially listed—remove the comment if you need this check.
• The ORDER BY clause organizes results by subject, then class, then student number for easier reading.

2. Show Whole Class Grades for a Specific Subject (Filtered by Student_number and Subject) #

This query lets you input a specific student's number and a subject (either name or number) to retrieve all grades for that subject across the entire class. We can optionally limit results to the same class as the target student.

Query (using Subject_name): #

SELECT 
    s.Student_number,
    s.Student_name,
    s.Student_surname,
    g.Grade,
    sub.Subject_name
FROM 
    student s
JOIN 
    grades g ON s.Student_number = g.Student_number
JOIN 
    subject sub ON g.Subject_number = sub.Subject_number
WHERE 
    -- Replace with your target Student_number and Subject_name
    s.Student_number = '12345'
    AND sub.Subject_name = 'Mathematics'
    -- Optional: Uncomment below to only show grades from the target student's class
    -- AND s.Class = (SELECT Class FROM student WHERE Student_number = '12345')
ORDER BY 
    s.Class, s.Student_number;

Query (using Subject_number): #

If you prefer filtering by subject number instead of name, use this simplified version:

SELECT 
    s.Student_number,
    s.Student_name,
    s.Student_surname,
    s.Class,
    g.Grade
FROM 
    student s
JOIN 
    grades g ON s.Student_number = g.Student_number
WHERE 
    -- Replace with your target Student_number and Subject_number
    s.Student_number = '12345'
    AND g.Subject_number = 'SUB001'
    -- Optional: Uncomment below to only show grades from the target student's class
    -- AND s.Class = (SELECT Class FROM student WHERE Student_number = '12345')
ORDER BY 
    s.Class, s.Student_number;

Explanation: #

• Both queries link all three tables to pull in full student details, subject info, and grades.
• The WHERE clause lets you specify the exact student and subject you're interested in.
• The optional subquery restricts results to the target student's class—remove the comment if you want to see grades for the subject across all classes instead.

内容的提问来源于stack exchange，提问作者Nerijus Pūras