MySQL在加入表-库存事务/库存系统上得到差异

Question

我不知道该怎么问这个问题，但我还是会试试。我有一个数据库结构，跟踪库存的每个产品，产品批次和仓库位置。这可以让我确切地知道哪些产品有库存，价格是多少，在什么地方。

基本上我使用两个表来跟踪

• stock_transactions [id, product_id, lot_id, warehouse_location_id, type, quantity]

  1. product_id =仓库中SKU商品的唯一标识符
  2. lot_id =到货批次的唯一标识符。
  3. warehouse_location_id =仓库位置唯一标识符
  4. type = type of transaction [IN = into location, OUT = OUT of location]

• stock_transactions_serials [stock_transaction_id serial_id]":

我在stock_transactions中为我们从仓库位置移动到位置的每个项目创建一个条目。例如，如果我将5个产品A从位置X移动到位置y，我将创建两个条目:

INSERT INTO stock_transactions 
(id, product_id, lot_id, warehouse_location_id, type, quantity) 
VALUES (1, 'A', 'Aq2er4', 'X', 'Out', -5)
INSERT INTO stock_transactions 
(id, product_id, lot_id, warehouse_location_id, type, quantity) 
VALUES (1, 'A', 'Aq2er4', 'Y', 'In', 5)

我们通过序列号跟踪一些产品。例如，如果上面的产品有序列号，我们将使用条形码阅读器扫描我们已移动到另一个位置的产品A的哪些序列号。表stock_transactions_serials将跟踪每个股票交易的序列号。

现在我有两个问题需要解决:

1。我需要获得产品和库存数量的列表(按product_id, lot_id和location_id分组)。我是这样做到的:

SELECT id, product_id, lot_id, warehouse_location_id, sum(ST.quantity) as qty
FROM stock_transactions ST 
GROUP BY product_id, lot_id, warehouse_location_id
HAVING qty > 0

我已经创建了SQLFiddle与一些数据在这里:http://sqlfiddle.com/#!9/fb352/2。

2。我得弄到那批货的序列号。我不知道如何解决这个问题。

如果我连接表，我没有得到正确的结果，或者我的股票SUM是错误的。什么好主意吗?我需要以某种方式获得In和Out事务之间的差异，并获得正确的序列。

Answer 1

似乎product_id + lot_id + warehouse_location_id是stock_transactions的自然键。您选择添加代理id，因此可以通过其自然键或其ID唯一地标识一条记录。您选择按自然键分组，但是您也可以按id分组。这个假设正确吗?

在这种情况下，您可以简单地汇总每个stock_transactions.id的序列号(即连接它们)并加入此列表:

SELECT
  ST.id, 
  ST.product_id, 
  ST.lot_id, 
  ST.warehouse_location_id, 
  sum(ST.quantity) as qty,
  STS.serials
FROM stock_transactions ST 
LEFT JOIN 
(
  SELECT stock_transaction_id, GROUP_CONCAT(serial_id) AS serials
  FROM stock_transactions_serials
  GROUP by stock_transaction_id
) STS ON STS.stock_transaction_id = ST.id 
GROUP BY ST.product_id, ST.lot_id, ST.warehouse_location_id
HAVING qty > 0;

Answer 2

我认为你只需要一个JOIN:

SELECT st.product_id, st.lot_id, st.warehouse_location_id, 
       st.quantity as qty
       GROUP_CONCAT(sts.serial_id) as serials
FROM stock_transactions st JOIN
     stock_transactions_serial sts
     ON sts.stock_transaction_id = st.id
GROUP BY st.product_id, st.lot_id, st.warehouse_location_id, st.quantity
HAVING qty > 0;

注意:这将生成一个以逗号分隔的序列号列表。