mysql - 销售期间的库存计算

Question

我有一个具有以下架构的 StockCard 表:

create table StockCard(
    id int(9) zerofill primary key auto_increment,
    ref_page smallint(3) not null,
    ref_number int(9) not null,
    sur_key int unsigned null,
    description varchar(255),
    warehouse_product_id int(9) zerofill not null,
    sc_date datetime not null,
    qty int not null,
    price double(15,2) not null,
    reserved_qty int not null,
    left_qty int not null,
    status boolean not null, /* true = buy, false = sell*/
    CONSTRAINT FOREIGN KEY (warehouse_product_id) REFERENCES Warehouse_Product(id),
    CONSTRAINT FOREIGN KEY (ref_page) REFERENCES Page(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在，当我想销售产品时，我使用 LIMIT 来了解我是否有足够的 left_qty/stock 来销售该产品。下面是我的查询，它在 for 循环 中运行，只要 avail_stock 仍低于 ordered_qty:

SELECT SUM(StockCard.left_qty) AS avail_qty FROM StockCard 
LEFT JOIN Warehouse_Product ON Warehouse_Product.id = StockCard.warehouse_product_id
WHERE Warehouse_Product.product_id = {product_id}
AND StockCard.status = 1 AND left_qty > 0
ORDER BY sc_date ASC, id ASC
LIMIT {row_limit} 
//this row_limit will increase in every loop as long as the SUM smaller than the ordered qty

我的问题是，使用我上面的方法是否真的会提高我的查询性能，或者我应该只删除 LIMIT？随意提供其他方法..

这是我的问题的简化版本:http://sqlfiddle.com/#!9/36ef5

Answer 1

不要使用LIMIT，它会产生很多查询。见 fiddle here ，首先你需要计算之前的总和(参见 x2 或 minqty)以及之前和当前的总和(参见 x1 或 maxqty) 然后使用它们来过滤你需要的东西。

SELECT x3.*
FROM
    ( SELECT sc1.*, IFNULL(SUM(sc2.left_qty),0) maxqty
    FROM StockCard sc1
      LEFT JOIN StockCard sc2 ON sc2.id <= sc1.id
    WHERE sc1.status = 1 AND sc2.status = 1 AND sc1.warehouse_product_id = 1 AND sc2.warehouse_product_id = 1
    GROUP BY sc1.id
    ORDER BY sc2.sc_date ASC, sc2.id ASC ) x1
  LEFT JOIN
    ( SELECT sc1.*, IFNULL(SUM(sc2.left_qty),0)+1 minqty
    FROM StockCard sc1
      LEFT JOIN StockCard sc2 ON sc2.id < sc1.id
    WHERE sc1.status = 1 AND sc2.status = 1 AND sc1.warehouse_product_id = 1 AND sc2.warehouse_product_id = 1
    GROUP BY sc1.id
    ORDER BY sc2.sc_date ASC, sc2.id ASC ) x2
  ON x1.id = x2.id
  LEFT JOIN StockCard x3 ON x3.id <= x1.id 
    AND x3.warehouse_product_id = sc1.warehouse_product_id
WHERE IFNULL(x2.minqty,0) <= 15 -- > change these
  AND x1.maxqty >= 15 --> change these

例如，当表包含:15、2 和 3 时，最小数量和最大数量将是:

qty min max
15    1  15
 2   16  17
 3   18  21