MySQL查询去重问题：指定学生修读课程总学分聚合计算

Hey there! Let's work through your problem clearly—sounds like you're stuck on avoiding duplicate data when calculating a student's total earned credits, and you can't rely on the tot_creds field in the student table. Let's break this down.

Why You're Seeing Duplicates #

First, the duplicate data issue almost certainly comes from the takes table (where student-course enrollments are stored). If a student retook a course multiple times (like for a better grade), each enrollment creates a separate row in takes. If you just join takes to course and sum the credits directly, you'll count that course's credit multiple times, inflating your total.

Correct SQL Query #

The fix is to first isolate the unique courses the student has taken, then join to the course table to sum the credits. Here's the clean approach:

-- Calculate total unique course credits for student ID '20084'
SELECT SUM(c.credits) AS total_credits
FROM (
    -- Get all distinct courses the student has enrolled in
    SELECT DISTINCT course_id
    FROM takes
    WHERE id = '20084'
) AS student_unique_courses
JOIN course c ON student_unique_courses.course_id = c.course_id;

Adding Context for Completed Courses (Optional) #

If your assignment requires only counting courses the student successfully completed (e.g., excluding failed grades), you can add filters to the subquery to exclude incomplete or failed enrollments:

SELECT SUM(c.credits) AS total_credits
FROM (
    SELECT DISTINCT course_id
    FROM takes
    WHERE id = '20084'
      AND grade IS NOT NULL -- Exclude courses with no final grade
      AND grade != 'F' -- Exclude failed courses (adjust based on your grading scale)
) AS student_completed_courses
JOIN course c ON student_completed_courses.course_id = c.course_id;

How This Works #

1. The subquery uses DISTINCT to pull only one entry per course the student has taken, eliminating duplicate enrollment rows.
2. We then join this filtered list to the course table to get the credit value for each unique course.
3. Finally, SUM() aggregates the credits to get the total.

This approach ensures you don't double-count credits from repeated course enrollments, which was likely causing your incorrect results earlier.

内容的提问来源于stack exchange，提问作者Dave C