mysql INNER JOIN上的值错误
|
我在gfee和netpay上获得了错误的价值。
SELECT s.id, s.name, c.name AS course_name,
s.open_bal AS open_balance, sum(i.amount) AS gross_fee,
sum(i.discount) AS discount, sum(i.amount) - sum(i.discount) AS net_payable,
SUM(r.reg_fee+r.tut_fee+r.other_fee) AS net_recieved,
(sum(i.amount) - sum(i.discount)) - SUM(r.reg_fee+r.tut_fee+r.other_fee) AS balance_due
FROM subscribers s
INNER JOIN courses c on c.id = s.course_id
LEFT JOIN invoices i on i.student_id = s.id
LEFT JOIN recipts r on r.student_id = s.id
GROUP BY s.id;
没有找到相关结果
已邀请:
3 个回复
捻盒愧杯
SELECT s.id , s.name , c.name AS course_name , s.open_bal AS open_balance , igroup.gross_fee , igroup.discount , igroup.net_payableinvoices , rgroup.net_recieved , igroup.net_payableinvoices - rgroup.net_recieved AS balance_due FROM students s INNER JOIN courses c on c.id = s.course_id LEFT JOIN ( SELECT i.student_id , SUM(i.amount) AS gross_fee , SUM(i.discount) AS discount , SUM(i.amount) - sum(i.discount) AS net_payableinvoices FROM invoices i GROUP BY i.student_id ) AS igroup ON igroup.student_id = s.id LEFT JOIN ( SELECT r.student_id , SUM(r.reg_fee+r.tut_fee+r.other_fee) AS net_recieved FROM recipts r GROUP BY r.student_id ) AS rgroup ON rgroup.student_id = s.id ;可扇胆
或表中有两行带有。佬棠
中的每一行（分别针对此特定记录）连接到中的两个不同行，则最终会包含两个条目。你的钱不是你的想法。