本文介绍了MySQL的内部联接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用以下查询从数据库的不同表中获取报告,请检查以下内容...
I am using following query to get report from different table of my database, check following...
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 students 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;发票
| id | student_id | amount | discount | dnt | +----+------------+----------+----------+-------------+ | 2 | 22 | 35000 | 0 | 2011/01/01 | +----+------------+----------+----------+-------------+未从Gross_fee和net_payable中获得正确的值.
Not getting correct value from gross_fee and net_payable.
谢谢.
推荐答案鉴于select中的SUM,我想GROUP BY s.id应该可以解决问题.无论如何,GROUP BY似乎丢失了:)
Given the SUMs in the select I suppose that GROUP BY s.id should do the trick. Anyway a GROUP BY seems to be missing :)
SELECT s.id, s.name, c.name AS course_name, s.open_bal AS open_balance, 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 students 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;编辑
单独的查询,允许检索所有发票的 gross_fee 和 net_payable
Separate query allowing to retrieve gross_fee and net_payable for all invoices
SELECT sum(amount) AS gross_fee, sum(discount) AS discount, sum(amount) - sum(discount) AS net_payable, FROM invoices;更多推荐
MySQL的内部联接
发布评论