mysql - 如何获取产品名称、数量和余额？

Question

我有3张 table

DMZ:

Ndm int - number of Document
Ddm date - date of supply
Pr int - 1(income), 2(expense)

DMS:

Id int
Kol decimal - quantity of product
Price decimal
Ndm int - number of document foreign key with DMZ
Ktov int - id of product foreign key with TOV

TOV:

Ktov - id if product
Ntov - name of product

我需要得到余额是收入与支出之间的差额。

结构:

1. 产品名称 (Ntov)
2. 残留量
3. 余额金额精确到分(差额销售期间花费的金额和收到的金额之间的差异)

按 Ntov 排序，按每个产品，对于有收入和可能有支出的每种产品，报告中会有一个摘要行

现在我有这个 sql 请求，但我被卡住了:/请帮助我，我做错了什么？

SELECT
 Ntov AS Product,
 SUM(CASE WHEN Pr = 1 then Kol*Price ELSE 0 END) AS Income,
 SUM(CASE WHEN Pr = 2 then Kol*Price ELSE 0 END) AS Expense,
 COUNT(DMS.Kol) AS LeftProducts
FROM DMS LEFT JOIN TOV
on DMS.Ktov = Tov.Ktov 
LEFT JOIN DMZ on DMS.Ndm = DMZ.Ndm
GROUP BY Ntov, Kol, Price
ORDER BY Product

DMS数据:http://prntscr.com/qb3qiq
DMZ数据:http://prntscr.com/qb3qog
TOV数据:http://prntscr.com/qb3qrl
结果:http://prntscr.com/qb3q92

Answer 1

您可以聚合并进行条件求和。考虑:

select
    t.ntov,
    sum(case z.pr when 1 then s.kol when 2 then - s.kol else 0 end) residue,
    sum(case z.pr when 1 then s.price when 2 then - s.price else 0 end) balance
from tov t
left join dms s on t.ktov = s.ktov
left join dmz z on s.ndm = z.ndm
group by t.ntov, t.ktov
order by t.ntov