mysql 内连接与 group by

发布于 2024-11-02 22:37:38 字数 883 浏览 6 评论 0原文

我正在使用以下查询来获取每批的正确结果。例如，如果我想查看 2010 批次的发票总额...

SELECT COALESCE(sum(i.amount),0) AS amount, 
    COALESCE(sum(i.discount),0) AS discount,
    COALESCE(sum(i.amount) - sum(i.discount),0) AS netpay,
    b.name AS batch
FROM fm_batches b
    INNER JOIN fm_invoices i
    LEFT JOIN fm_students s ON i.student_id = s.id
GROUP BY b.name

及其输出以下结果...

| amount | discount | netpay   | batch | 
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2011  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2010  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2009  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2008  |
+--------+----------+----------+-------+

我确信我在查询中做错了什么，因为它给出了错误的结果。如果 2010 批次没有找到任何内容，它应该返回 0。谢谢。

原文

I am using the following query to get correct results for each batch. For example if I would to like to see the total of invoice for batch 2010...

SELECT COALESCE(sum(i.amount),0) AS amount, 
    COALESCE(sum(i.discount),0) AS discount,
    COALESCE(sum(i.amount) - sum(i.discount),0) AS netpay,
    b.name AS batch
FROM fm_batches b
    INNER JOIN fm_invoices i
    LEFT JOIN fm_students s ON i.student_id = s.id
GROUP BY b.name

And its outputting the following results...

| amount | discount | netpay   | batch | 
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2011  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2010  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2009  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2008  |
+--------+----------+----------+-------+

I am sure that I am doing something wrong in my query as its giving the wrong results. It should return 0 if nothing found for batch 2010. thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨小墨 2024-11-09 22:37:38

所以你需要类似的东西：（

SELECT COALESCE(sum(i.amount),0) AS amount, 
    COALESCE(sum(i.discount),0) AS discount, 
    COALESCE(sum(i.amount)-sum(i.discount),0) AS netpay, 
    b.name AS batch 
FROM batches b 
LEFT JOIN subscribers s on s.bacth_id = b.id
LEFT JOIN invoices i on i.subs_id = s.id
GROUP BY b.name

猜测订阅者和批次之间的关系）。

So you need something like:

SELECT COALESCE(sum(i.amount),0) AS amount, 
    COALESCE(sum(i.discount),0) AS discount, 
    COALESCE(sum(i.amount)-sum(i.discount),0) AS netpay, 
    b.name AS batch 
FROM batches b 
LEFT JOIN subscribers s on s.bacth_id = b.id
LEFT JOIN invoices i on i.subs_id = s.id
GROUP BY b.name

(guessing the relationship between subscribers and batches).

回复收藏 0 原文

~没有更多了~