LEFT JOIN查询未返回totalpay：如何获取2024年学生购书总支付额？

问题所在 #

你的SQL查询结果里没有totalpay字段，核心原因是SELECT子句中未包含这个字段。你仅指定了id_student、name_student、date_insc_student和payment_student四个字段，所以查询结果不会输出子查询中计算的totalpay。

修改后的查询语句 #

方案一：保留原结构，添加目标字段

SELECT id_student, name_student, date_insc_student, payment_student, tblpay.totalpay
FROM tblstud
LEFT JOIN 
( SELECT SUM(amount_payment) AS totalpay, idstud_payment FROM tblpay GROUP BY idstud_payment ) AS tblpay 
ON tblpay.idstud_payment = tblstud.id_student 
WHERE YEAR(tblstud.date_insc_student) = 2024 
ORDER BY date_insc_student, name_student 

• 说明：将tblpay.totalpay加入SELECT列表后，结果就会显示每位学生的购书总支付额。如果只需要筛选有付费记录的学生，把LEFT JOIN改为INNER JOIN，即可自动排除无支付记录的学生。

方案二：更简洁的直接分组写法

SELECT 
    s.id_student, 
    s.name_student, 
    s.date_insc_student, 
    s.payment_student,
    SUM(p.amount_payment) AS totalpay
FROM tblstud s
LEFT JOIN tblpay p ON p.idstud_payment = s.id_student
WHERE YEAR(s.date_insc_student) = 2024
GROUP BY s.id_student, s.name_student, s.date_insc_student, s.payment_student
ORDER BY s.date_insc_student, s.name_student

• 说明：直接通过主表关联支付表，再分组计算总支付额，结构更清晰，无需嵌套子查询。同样，换成INNER JOIN可只保留有付费记录的学生。

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