我有2个表Tblinkreceiving和Tblinkdelivery。我想显示每种墨水代码的库存余额。我尝试了以下sql join查询,但是当我在手动计算中进行交叉检查时,许多墨水代码的计算是错误的。
select r.inkcode, SUM(r.quantity) Stock-In, SUM(d.quantity) Stock-out, (SUM(r.quantity) - SUM(d.quantity)) Stock-Balance from Tblinkreceiving r,Tblinkdelivery d where r.inkcode=d.inkcode
group by r.inkcode;
最佳答案
WITH i AS
(
SELECT inkcode, SUM(quantity) AS qin
FROM tblInkReceiving
GROUP BY
inkcode
),
o AS
(
SELECT inkcode, SUM(quantity) AS qout
FROM tblInkDelivery
GROUP BY
inkcode
)
SELECT COALESCE(i.inkcode, o.inkcode) AS inkcode,
COALESCE(qin, 0) AS stock_in,
COALESCE(qout, 0) AS stock_out,
COALESCE(qin, 0) - COALESCE(qout, 0) AS stock_balance
FROM i
FULL JOIN
o
ON o.inkcode = i.inkcode
关于sql-server - 计算每种产品(墨水代码)的库存,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/10294393/