mysql - mysql中的复杂查询用于按日期生成报告

Question

我在 mysql 中有以下表:

销售发票(包含发票编号、分类帐和日期)

销售发票项目(带有发票编号)

付款(包含发票号码、账本和日期)

现在我需要通过使用税收等的计算(表中包含的列)来根据特定分类帐的特定发票编号来计算 salesinvoiceitems 表中的总金额。

然后我有一个付款表，用于维护按日期对特定发票进行的所有付款的记录。该表还包含发票编号和分类帐 ID。

我需要为特定分类帐 ID 生成显示最终余额的报告。我对这样的询问一无所知。请阐明一些情况。

Answer 1

我假设您的 salesinvoiceitems 表有一个“金额”字段，以便我们可以计算每张发票的项目金额总和。在下面的示例中，我将该列称为“item_amt”。我们可以尝试做这样的事情......

select ledgerid , sum(balance) as total_balance
from 
-- sub query to calculate balance per invoice and ledgerid
(
select distinct 
 invoices.invoice_number, 
 invoices.ledgerid, 
 tot_item_amt, 
 tot_payment_amt, 
 (tot_item_amt - tot_payment_amt) as balance
from salesinvoices as invoices
-- sub query to get the sum of all the items on an invoice
inner join (select invoice_number, sum(item_amt) as tot_item_amt from salesinvoiceitems group by invoice_number) as items on invoices.invoice_number = items.invoice_number
-- sub query to get the sum of all the payments made against an invoice
inner join (select invoice_number, sum(payment_amt) as tot_payment_amt from payments group by invoice_number) as payments on invoices.invoice_number = payments.invoice_number
) t
-- sum balance and group by ledgerid
group by ledgerid