SQL 查询每个学生所选课程的最高成绩

To query the highest score of each course for every student, you would need to join the tables that contain student information, course information, and scores. Assuming you have three tables named 'students', 'courses', and 'scores' with the following structures:

Table: students

• student_id (primary key)
• student_name

Table: courses

• course_id (primary key)
• course_name

Table: scores

• student_id (foreign key referencing students.student_id)
• course_id (foreign key referencing courses.course_id)
• score

You can use the following SQL query to achieve the desired result:

SELECT students.student_id, students.student_name, scores.course_id, MAX(scores.score) AS highest_score FROM students JOIN scores ON students.student_id = scores.student_id GROUP BY students.student_id, students.student_name, scores.course_id;

This query joins the 'students' and 'scores' tables based on the student_id column. It then groups the results by student_id, student_name, and course_id to calculate the maximum score for each course. The MAX function is used to obtain the highest score. The result will include the student_id, student_name, course_id, and highest_score columns.