库存库存计算查询- Mysql

Question

我有两个单独的sql查询来计算总购买量和总销售量。

    SELECT b.book_id, b.title, SUM(pid.quantity) AS 'Purchase Quantity'
    FROM purchase_invoice_det pid JOIN book b ON pid.book_id = b.book_id
    JOIN purchase_invoice pi ON pid.purchase_inv_id = pi.purchase_inv_id 
    WHERE pi.purchase_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title;


    SELECT b.book_id, b.title, SUM(sid.quantity) AS 'Sold Quantity'
    FROM sales_invoice_det sid JOIN book b ON sid.book_id = b.book_id
    JOIN sales_invoice si ON sid.sales_inv_id = si.sales_inv_id
    WHERE si.sales_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title;

现在，为了计算每种产品的可用库存，我需要通过组合两个查询从购买数量中减去总售出数量。

我尝试按如下方式加入，但它将可用库存计算为原始可用数量的2倍。

    SELECT b.book_id, b.title, SUM(pid.quantity)-SUM(sid.quantity) AS 'Available Stock',
    FROM purchase_invoice_det pid JOIN book b ON pid.book_id = b.book_id
    JOIN purchase_invoice pi ON pid.purchase_inv_id = pi.purchase_inv_id
    JOIN sales_invoice_det sid ON sid.book_id = b.book_id
    JOIN sales_invoice si ON sid.sales_inv_id = si.sales_inv_id 
    WHERE pi.purchase_inv_date BETWEEN '2020-02-01' AND '2020-12-31'
    AND si.sales_inv_date BETWEEN '2020-01-01' AND '2020-12-31' 
    AND pi.branch_id = 2 AND si.branch_id = 2 GROUP BY b.title;

你知道如何结合以上两个sql查询来计算每种产品的可用库存吗？

Answer 1

尝试这种方法。

SELECT A.BOOK_ID,B.TITLE,A.Purchase_Quantity-B.Sold_Quantity from (SELECT b.book_id, b.title, SUM(pid.quantity) AS Purchase_Quantity
    FROM purchase_invoice_det pid JOIN book b ON pid.book_id = b.book_id
    JOIN purchase_invoice pi ON pid.purchase_inv_id = pi.purchase_inv_id 
    WHERE pi.purchase_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title) A , 
    (SELECT b.book_id, b.title, SUM(sid.quantity) AS Sold_Quantity
    FROM sales_invoice_det sid JOIN book b ON sid.book_id = b.book_id
    JOIN sales_invoice si ON sid.sales_inv_id = si.sales_inv_id
    WHERE si.sales_inv_date BETWEEN '2020-01-01' AND '2020-12-31' AND branch_id = 2 GROUP BY title)B
WHERE  A.BOOK_ID=B.BOOK_ID AND A.TITLE =B.TITLE ;