sql-server - 计算每个产品(Inkcode)库存

Question

我有 2 个表 Tblinkreceiving 和 Tblinkdelivery。我想显示每个 inkcode 的库存余额。我尝试了下面的 sql 连接查询，但当我交叉检查手动计算时，许多 inkcode 的计算都是错误的。哪里出了问题？

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;

Answer 1

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