在查询中花费大量时间来获得结果。我只收到了错误的答案。
分类帐表
明细|cr|dr|tds|借记
p-01|500|0|50|50
p-02|1500|0|0|0
p-01|7500|0|0|0
静止|0|100|0|0
账单明细表
bill_no|party_com_name|total_cost
BILL-01 | p-01 | 2500
BILL-02|p-02|4000
BILL-03 | p-01 | 9000
聚会桌
p_id |聚会_com_name
p-01|ABC
p-02|XYZ
我希望输出为
party_com_name|total_cost|paidamt|pendingamt
AA | 11500 | 8000 | 3500
BB |4000|1500|2500
上述结果的公式：
total_cost=来自具有相同名称的账单明细的所有total_co斯特的总和
paidamt=cr+tds+来自同名分类账的借方之和
pendingamt=来自结果的total_cost-paidamt
请帮助
试试这个：
SELECT
Billing_details.party_com_name,
SUM(Billing_details.total_cost) AS total_cost,
SUM(Ledger.cr) AS paidamt,
(total_cost - paidamt) AS pendingamt
FROM
Billing_details
INNER JOIN Ledger
ON Ledger.bill_no = Billing_details.bill_no
GROUP BY Billing_details.party_com_name;
查询：-
SELECT billing_details.party_com_name,SUM(billing_details.`total_cost`) `total_cost`, SUM(ledger_table.`cr`) `paidamt` , ((SUM(billing_details.`total_cost`))-(SUM(ledger_table.`cr`))) as `pendingamt`
FROM billing_details
INNER JOIN ledger_table
ON
billing_details.bill_no = ledger_table.bill_no
GROUP BY billing_details.party_com_name