mysql - mysql中的计算和多重连接

Question

1.products(产品表)

1.productid 

  001         
  002         
  003         

2.productname

  x
  y
  z

2.invoiceqty(发票数量表)

1.productid

  001       
  001      
  002       
  002 

2.invqty

  10
  20
  10
  05

3.grnqty(GRN数量表)

1.productid 

  001        
  002        
  003        

2.grqty

  50
  50
  50

4.returninqty(返回数量表)

1.productid 

  002
  002

2.retinqty

  02
  03

5.returnoutqty(返回数量表)

1.productid 

  001
  001

2.retoutqty

  01
  01

我需要执行此查询来查看库存余额库存余额计算如下

(grnqty+returninqty)-(invoiceqty+returnoutqty) = StockBalance

请告诉我如何编写这样的查询来计算库存余额我已经用 java/MySQL 开发了一个库存控制系统，请帮助我解决这个问题

6.股票(查询)

1.productid 

  001
  002
  003

2.Stockqty

  40
  33
  50

Answer 1

尝试使用子查询计算每个表的数量。它不适用于 LEFT JOIN，因为我们将在表中使用笛卡尔积。

SELECT
    p.productid,
(
    SELECT COALESCE(SUM(grnqty), 0) 
    FROM grnqty g 
    WHERE g.productid = p.productid
) 
 +
(
    SELECT COALESCE(SUM(returninqty), 0) 
    FROM returninqty ri 
    WHERE ri.productid = p.productid
)     
 - 
(
    SELECT COALESCE(SUM(invoiceqty), 0) 
    FROM invoiceqty i 
    WHERE i.productid = p.productid
)
 -
(
    SELECT COALESCE(SUM(returnoutqty), 0) 
    FROM returnoutqty ro 
    WHERE ro.productid = p.productid
) as stock

FROM products p

此外，您可以创建一个函数来计算库存。您可以轻松地将其重用于其他查询。

DELIMITER $$

CREATE FUNCTION `get_product_stock` (IN p_id_product INT) RETURNS INT
BEGIN

DECLARE stock INT;

SET stock =
    (
        SELECT COALESCE(SUM(grnqty), 0)
        FROM grnqty
        WHERE productid = p_id_product
    )
     +
    (
        SELECT COALESCE(SUM(returninqty), 0)
        FROM returninqty
        WHERE productid = p_id_product
    )
     -
    (
        SELECT COALESCE(SUM(invoiceqty), 0)
        FROM invoiceqty
        WHERE productid = p_id_product
    )
     -
    (
        SELECT COALESCE(SUM(returnoutqty), 0)
        FROM returnoutqty
        WHERE productid = p_id_product
    );

RETURN stock;

END $$

DELIMITER ;

您可以像这样使用该函数:

// get the stock for product with id 1
SELECT get_product_stock(1); 

// get all the products with their stock
SELECT productid, name, get_product_stock(productid) as stock FROM products;