MySQL查询帮助需要

Question

可能重复：
complex my sql query wrong resultsMySQL查询帮助需要

我试图构建复杂的MySQL查询，但其返回错误结果...

SELECT b.name AS batch_name, b.id AS batch_id, 
     COUNT(DISTINCT s.id) AS total_students, 
     COALESCE(sum(s.open_bal), 0) AS open_balance, 
     sum(COALESCE(i.reg_fee,0) + COALESCE(i.tut_fee,0) + COALESCE(i.other_fee,0)) AS gross_fee, 
     sum(COALESCE(i.discount,0)) AS discount, 
     COALESCE(sum(s.open_bal), 0) + sum(COALESCE(i.reg_fee,0) + COALESCE(i.tut_fee,0) + COALESCE(i.other_fee,0)) - sum(COALESCE(i.discount,0)) AS net_payable, 
     sum(COALESCE(r.reg_fee,0) + COALESCE(r.tut_fee,0) + COALESCE(r.other_fee,0)) AS net_recieved, 
     (COALESCE(sum(s.open_bal), 0) + sum(COALESCE(i.reg_fee,0) + COALESCE(i.tut_fee,0) + COALESCE(i.other_fee,0)) - sum(COALESCE(i.discount,0))) - (sum(COALESCE(r.reg_fee,0) + COALESCE(r.tut_fee,0) + COALESCE(r.other_fee,0))) AS balance_due 
     FROM batches b 
     LEFT JOIN students s on s.batch = b.id 
     LEFT JOIN invoices i on i.student_id = s.id 
     LEFT JOIN recipts r on r.student_id = s.id 
     WHERE s.inactive = 0 
     GROUP BY b.name, b.id; 

我是因为sum（open_bal）结果是错误的，所以做错了。

样品结果...

| batch_name | total_students | open_bal | gross_fee | discount | net_payable | net_recieved | due_balance | 
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+ 
| MS  | 6    | 10000 | 0   | 0  | 10000  | 101000  | -91000  | 
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+ 

以上结果是错误的，请检查下面的表结构

学生表

| id | open_bal | batch | 
+-----+----------+-------+ 
| 44 | -16000 | 9  | 
+-----+----------+-------+ 
| 182 | 9000  | 9  | 
+-----+----------+-------+ 
| 184 | -36000 | 9  | 
+-----+----------+-------+ 
| 185 | 19000 | 9  | 
+-----+----------+-------+ 
| 186 | 9000  | 9  | 
+-----+----------+-------+ 
| 187 | 4000  | 9  | 
+-----+----------+-------+ 

发票表

| id | student_id | reg_fee | tut_fee | other_fee | net_payable | discount | 
+------+------------+---------+---------+-----------+-------------+----------+ 
|  |   |   |   |   |    |   | 
+------+------------+---------+---------+-----------+-------------+----------+ 

没有发票可用于以上学生ID。

Recipts表

| id | student_id | reg_fee | tut_fee | other_fee | status  | 
+------+------------+---------+---------+-----------+------------+ 
| 8 | 44   | 0  | 0  | 1500  | confirmed | 
+------+------------+---------+---------+-----------+------------+ 
| 277 | 44   | 0  | 50000 | 0   | confirmed | 
+------+------------+---------+---------+-----------+------------+ 
| 26 | 182  | 0  | 0  | 1500  | confirmed | 
+------+------------+---------+---------+-----------+------------+ 
| 424 | 182  | 0  | 15000 | 0   | confirmed | 
+------+------------+---------+---------+-----------+------------+ 
| 468 | 182  | 0  | 15000 | 0   | confirmed | 
+------+------------+---------+---------+-----------+------------+ 
| 36 | 185  | 0  | 0  | 1500  | confirmed | 
+------+------------+---------+---------+-----------+------------+ 
| 697 | 185  | 0  | 15000 | 0   | confirmed | 
+------+------------+---------+---------+-----------+------------+ 
| 66 | 187  | 0  | 0  | 1500  | confirmed | 
+------+------------+---------+---------+-----------+------------+ 

使用上面的SQL查询和表格预期结果...

| batch_name | total_students | open_bal | gross_fee | discount | net_payable | net_recieved | due_balance | 
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+ 
| MS  | 6    | -11000 | 0   | 0  | 10000  | 101000  | -112000  | 
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+ 

请检查并回复，谢谢。

Answer 1

您必须将student_id添加到GROUP BY表达式中。

您正在创建多个外连接，这意味着所有数据将用于查询中，但收据表中的某些学生也有多行，因此它们的金额会被复制（因为会有与该s.id几行）。

采取以下加入一看：

 r.id  s.id  open_bal 
     -------------------------- 
     8  | 44 | -16000 
     277  | 44 | -16000 
     26  | 182 | 9000 
     424  | 182 | 9000 
     468  | 182 | 9000 
     36  | 185 | 19000 
     697  | 185 | 19000 
     66  | 187 | 4000 
     null | 184 | -36000 
     null | 186 | 9000 

概括起来，你会被前面的问题（以上）的同一用户获得10万