我需要了解客户的待付款总额。我有两张表:一张是发票,二张是收到的付款。
这是发票表的模式
inv_no | date | customer_name | total_baic | freight_rate | delivery_rate
这是支付表的模式
id | date | customer_name | payment_received | inv_no
我需要的是暂挂客户,如customer_name,暂挂总额,其中暂挂总额为(total_basic+freight_rate+delivery_rate(-(payment_received(
我所尝试的是两者都不同,但不知道如何做这个减法部分
这是从发票表中查询待定
SELECT Sum(invoice.freight_rate) + sum(invoice.total_basic_amount) + sum(invoice.delivery_rate) + sum(invoice.advanced_amount) AS totalpending FROM invoice GROUP BY invoice.client_name
以下是从付款表中查询付款收据
SELECT Sum(payments.payment_received) AS totalreceived FROM payments GROUP BY payments.client_name
如何像totalpending-totalreceived一样进行二者的子串运算。
不知怎么的,我可以创建这个查询,但问题是这个查询返回双倍金额,你能检查吗
**SELECT invoice.client_name, Sum(invoice.freight_rate)+Sum(invoice.total_basic_amount)+Sum(invoice.delivery_rate) AS totalpending, Sum(payments.payment_received) AS totalpayment FROM invoice, payments GROUP BY invoice.client_name, payments.client_name**
我得到
client_name | totalpending | totalpayment
John | 1800 | 1100
这里的总付款是正确的,因为在我的付款表中有两个条目,一个是600,另一个是500,所以是1100但在发票表中只有一个条目,其中总基本价格=600,运费=0交货率也为0所以这里的待处理总量必须是600
据我所知,这可能是您正在寻找的解决方案:
select invoice.customer_name, (sum(invoice.freight_rate) + sum(invoice.total_basic_amount)+ sum(invoice.delivery_rate) - sum(payments.payment_received)) as TotalPending
from invoice,payments where invoice.inv_no=payments.inv_no
此查询将为您获取客户名称及其待付款总额。