首页 / MYSQL / 复杂的MySQL查询错误的结果
复杂的MySQL查询错误的结果
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了复杂的MySQL查询错误的结果，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5931字，纯文字阅读大概需要9分钟。
内容图文
我正在尝试构建复杂的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;
返回以下结果……
| batch_name | total_students | open_bal | gross_fee | discount | net_payable | net_recieved | due_balance |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+
| MS | 6 | 10000 | 0 | 0 | 10000 | 101000 | -91000 |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+
批次表
| id | name |
+-----+------+
| 9 | Ms |
+-----+------+
学生表
| id | open_bal | batch | inactive |
+-----+----------+-------+----------+
| 44 | -16000 | 9 | 0 |
+-----+----------+-------+----------+
| 182 | 9000 | 9 | 0 |
+-----+----------+-------+----------+
| 184 | -36000 | 9 | 0 |
+-----+----------+-------+----------+
| 185 | 19000 | 9 | 0 |
+-----+----------+-------+----------+
| 186 | 9000 | 9 | 0 |
+-----+----------+-------+----------+
| 187 | 4000 | 9 | 0 |
+-----+----------+-------+----------+
发票表
| id | student_id | reg_fee | tut_fee | other_fee | net_payable | discount |
+------+------------+---------+---------+-----------+-------------+----------+
| | | | | | | |
+------+------------+---------+---------+-----------+-------------+----------+
上述学生ID没有发票.
收件表
| 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 |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+
解决方法:
你仍然没有提供完整的信息 – 没有批处理表,甚至没有现有的recipts表..无论如何,我认为我们不关心批处理表中的什么,让我们说它只是名称和ID.您的收据表为同一个学生提供了多行.由于所有JOIN,这应该导致为其他表返回多行.因此,SUM()多次为必须求和的值,即open_balance.这可能是问题出在何处的线索,我说你必须将你需要的信息从收据表移动到子查询中,但我不确定你是否向我们展示了你的整个数据库.尝试从查询中删除收据表并再次检查结果.如果就是这样,你应该看看从那里开始做什么或者至少给我们更多的信息.
编辑：
查询应该是：
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((SELECT SUM(COALESCE(receipts.reg_fee, 0)
+ COALESCE(receipts.tut_fee, 0)
+ COALESCE(receipts.other_fee, 0)) FROM receipts WHERE receipts.student_id = s.id))
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((SELECT SUM(COALESCE(receipts.reg_fee, 0)
+ COALESCE(receipts.tut_fee, 0)
+ COALESCE(receipts.other_fee, 0)) FROM receipts WHERE receipts.student_id = s.id))
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
WHERE s.inactive = 0
GROUP BY b.name, b.id;
这将汇总收据表中的学生数据,即使它在多行上,只返回一行.将联接删除到收据表将从其他表中删除重复的行,因此现在计算应该是正确的.
还有一件事 – 你在WHERE子句中得到s.inactive = 0,确保它与这个计算无关.
附：你怎么不知道子查询是什么,你最终写这样的东西？
内容总结
以上是互联网集市为您收集整理的复杂的MySQL查询错误的结果全部内容，希望文章能够帮你解决复杂的MySQL查询错误的结果所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】