如何获得产品名称、数量和余额？

Question

我有三张桌子

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

我需要得到平衡是收入和开支之间的差额。

结构：

产品名称(Ntov)

• Amount of

2. 余额金额准确到分值(销售期间支出和收到的差额)

按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

非军事区数据：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