我正在使用学说作为ORM层,但是通过在数据库工具中放置一个普通的MySQL查询,我得到了相同的结果。所以我的问题:
我有一个发票表,Invoice_Items和Invoice_payments表,因此我想获得的是所有尚未付款或至少尚未完全付款的发票。我知道查询应该几乎正确,因为它给出了正确数量的项目...唯一的是将数量乘以许多可能加入的行。
所以我现在有的查询:
select invoice.*, sum(item.amount * item.quantity) as totalDue,
sum(payment.amount) as totalPaid
from invoices as invoice
left join invoice_items as item on item.invoice_id = invoice.id
left join invoice_payments as payment on payment.invoice_id = invoice.id
and payment.status = 'successful'
where invoice.invoice_number is not null
and invoice.sent_at is not null
and invoice.due_date >= '2018-05-15'
group by invoice.id
having count(payment.id) = 0
or sum(payment.amount) < sum(item.amount * item.quantity)
order by invoice.issue_date desc, sum(payment.amount) desc;
您可以看到,我在选择中也有总计和总计(仅供参考,如果查询正确,应删除(。
。我看到的是该金额乘以六个(因为付款表中有6个项目(。
因此,也许有人可以帮助我指出正确的方向,因为它不会在总计上乘以倍数。我当时认为这可能是因为小组划分但没有我的查询失败。
通过简单地使用我的查询中的独特之处,我解决了问题。
select invoice.*, sum(distinct(item.amount * item.quantity)) as totalDue,
sum(payment.amount) as totalPaid
from invoices as invoice
left join invoice_items as item on item.invoice_id = invoice.id
left join invoice_payments as payment on payment.invoice_id = invoice.id
and payment.status = 'successful'
where invoice.invoice_number is not null
and invoice.sent_at is not null
and invoice.due_date >= '2018-05-15'
group by invoice.id
having count(payment.id) = 0
or sum(payment.amount) < sum(distinct(item.amount * item.quantity))
order by invoice.issue_date desc, sum(payment.amount) desc;
我要感谢所有花时间重新定义我的问题的人; - (