计算各产品（Inkcode）股票(Calculate the each product(Inkcod

我有2个表Tblinkreceiving和Tblinkdelivery。 我想显示每个inkcode.I库存余额尝试下面的SQL JOIN查询，但计算是错误的许多inkcodes当我交叉检查手动calculation.Where它在哪里呢？

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

Answer 2:

你使用了内部联接（隐含的），以接收和传递相结合。 这意味着，已收到，但并未提供任何油墨代码将从您的结果中排除。

如果你可以使任何墨水输送必须有一个墨收据（根据您不能提供，你有没有收到东西的前提下）的假设，那么你可以使用一个内连接上tblInkReceiving，具体如下：

SELECT r.inkcode, 
    SUM(r.quantity) AS Stock-In, 
    ISNULL(SUM(d.quantity), 0) AS Stock-out,
    SUM(r.quantity) - ISNULL(SUM(d.quantity), 0) AS Stock-Balance 
FROM Tblinkreceiving r LEFT JOIN Tblinkdelivery d ON r.inkcode = d.inkcode
GROUP BY r.inkcode

左联接将返回左（R），包括右侧（d）匹配记录的所有记录。 如果d没有匹配的记录（即出现了针对油墨代码没有墨水交货），然后对这些行中的值将是无效的。

如果你不能作一个假设，墨只能递送一旦被接收，那么你需要或者链接到油墨表以及（即表其inkcode是主键），或联合所有的墨水代码在接收和交付的表，并链接到的是：

;WITH cte AS (SELECT inkcode FROM Tblinkreceiving 
    UNION 
    SELECT inkcode FROM Tblinkdelivery)
SELECT cte.inkcode, 
    SUM(r.quantity) AS Stock-In, 
    ISNULL(SUM(d.quantity), 0) AS Stock-out,
    SUM(r.quantity) - ISNULL(SUM(d.quantity), 0) AS Stock-Balance 
FROM cte LEFT JOIN Tblinkreceiving r ON cte.ink_code = r.ink_code
    LEFT JOIN Tblinkdelivery d ON cte.inkcode = d.inkcode
GROUP BY cte.inkcode

文章来源: Calculate the each product(Inkcode) stock