MySQL内连接结合SUM函数返回错误值问题排查

嘿，我懂你碰到的问题了——直接用INNER JOIN搭配SUM函数的时候，很容易因为多表关联产生的笛卡尔积（比如一个学生选了好几门课，同时有好几条缴费记录，关联后每条记录会被重复计算），导致汇总出来的数值完全不对。我给你梳理下正确的解决思路和查询语句吧：

核心思路是先分别聚合「学生每学期应缴总费用」和「学生累计已缴费用」，再将两者关联计算欠费，避免直接多表连接带来的重复计算问题。

1. 先计算每个学生每学期的应缴总学费 #

从选课表关联课程表，按学生学号和学期分组，汇总该学期所有课程的学费：

SELECT 
    sic.roll_number,
    sic.semester,
    SUM(c.course_tuition_fees) AS total_due
FROM students_in_courses sic
JOIN courses c ON sic.course_name = c.course_name AND sic.semester = c.semester
WHERE sic.semester BETWEEN 1 AND 4
GROUP BY sic.roll_number, sic.semester

2. 再计算每个学生的累计已缴费用 #

从缴费记录表按学号分组，汇总所有缴费金额：

SELECT 
    roll_number,
    SUM(fee_paid) AS total_paid
FROM student_fee_payment
GROUP BY roll_number

3. 关联所有数据生成欠费报表 #

将上面两个子查询和学生表关联，最终得到包含欠费信息的完整报表：

SELECT 
    s.roll_number,
    CONCAT(s.first_name, ' ', COALESCE(s.middle_name, ''), ' ', s.last_name) AS full_name,
    sem.semester,
    sem.total_due,
    COALESCE(pay.total_paid, 0) AS total_paid,
    (sem.total_due - COALESCE(pay.total_paid, 0)) AS outstanding_balance
FROM students s
JOIN (
    -- 子查询：每个学生每学期的应缴费用
    SELECT 
        sic.roll_number,
        sic.semester,
        SUM(c.course_tuition_fees) AS total_due
    FROM students_in_courses sic
    JOIN courses c ON sic.course_name = c.course_name AND sic.semester = c.semester
    WHERE sic.semester BETWEEN 1 AND 4
    GROUP BY sic.roll_number, sic.semester
) sem ON s.roll_number = sem.roll_number
LEFT JOIN (
    -- 子查询：每个学生的累计已缴费用
    SELECT 
        roll_number,
        SUM(fee_paid) AS total_paid
    FROM student_fee_payment
    GROUP BY roll_number
) pay ON s.roll_number = pay.roll_number
WHERE (sem.total_due - COALESCE(pay.total_paid, 0)) > 0 -- 仅显示欠费学生
ORDER BY sem.semester, s.roll_number;

关键细节说明 #

• 为什么要拆分聚合再关联？如果直接把所有表一次性JOIN，会出现笛卡尔积：比如一个学生选3门课、有2条缴费记录，关联后会生成6条重复记录，SUM时费用和缴费都会被多算，导致数值错误。
• COALESCE(pay.total_paid, 0)是为了处理完全没有缴费记录的学生，避免出现NULL值影响计算。
• 如果你的student_fee_payment表实际包含semester字段（比如你漏写了），只需在已缴费用的子查询里加上semester分组，并在JOIN时匹配学期即可，调整后的已缴子查询示例：

SELECT 
    roll_number,
    semester,
    SUM(fee_paid) AS total_paid
FROM student_fee_payment
GROUP BY roll_number, semester

然后在主查询的LEFT JOIN条件里补充sem.semester = pay.semester。

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