mysql内部加入群组
问题描述:
我正在使用以下查询来获取每个批次的正确结果。例如,如果我会喜欢看总发票批2010 ...mysql内部加入群组
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 |
+--------+----------+----------+-------+
我相信,我在做什么我的查询错误,因为它提供了错误的结果。它应该返回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 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
(猜测用户和批次之间的关系)。
发票和批次之间的关系是什么......发票表上是否有批次栏? – DRapp 2011-04-25 10:42:43
发票JOIN没有ON子句。发票和批次/订阅之间的关系是什么? – Galz 2011-04-25 10:43:24
@DRapp不,我没有在发票表上的批次列,但我在订户表中,所以表格将订户,发票和批次 – seoppc 2011-04-25 10:44:25