时间戳

首页 > 解决方案 > 如何修理我的桌子以使药物过期？

mysql - 如何修理我的桌子以使药物过期？

问题描述

我正在做药店的库存系统。而现在我正在尝试做过期药。我想给你看我的桌子。也许你知道如何修理我的桌子。我将到期日期放在 tbl_received_order 中。当我收到药物时，它会插入 tbl_medicine。如果客户买了 50 片扑热息痛，或者supplier_med_id 等于 3。两个月后收到的药就会过期。我DATE(NOW()) > tbl_received_order.expiration_date用来拿所有过期的药。我的问题是与药物过期的qty时间不符。rec_qty仅举以下示例，我有 100 件。在我的初始库存中。但后来，50个。已经售出。为什么是100个。还在反映我的过期药品库存，而不是50个。我该如何修理我的桌子？有人可以帮我解决我的问题吗？

这是我的桌子

tbl_medicine

med_id  supplier_med_id  qty    status   branch_id
1       3                50     Active   1
2       7                100    Active   1
3       9                100    Active   1

tbl_received_order

received_id  user_id  purchase_order_id   date_received 
RO20190001   1        PO20190001          2019-05-04

tbl_received_order_details

id    received_id    supplier_med_id   rec_qty   expiration_date
1     RO20190001     3                 100       2019-11-04
2     RO20190001     7                 100       2019-11-04
3     RO20190001     9                 100       2019-11-04
1     RO20190002     3                 50        2019-11-04
2     RO20190002     7                 50        2019-11-04
3     RO20190002     9                 50        2019-11-04

tbl_transaction

transaction_id  customer_id  user_id  transaction_date  branch_id
0001            CU2018001    1        2019-09-04        1
0002            CU2018001    1        2019-09-04        1
0003            CU2018001    1        2019-09-04        1

tbl_transaction_details

details_id   transaction_id  supplier_med_id   qty    price    total_price
1            0001            3                 50     10.00    500
2            0002            3                 10     10.00    100
3            0003            3                 10     10.00    100

标签： mysql

解决方案

您可以使用JOIN.

SELECT orderd.expiration_date, orderd.rec_qty - SUM(trans.qty) AS inventory_left
FROM tbl_received_order_details AS orderd
JOIN tbl_transaction_details AS trans
ON orderd.supplier_med_id = trans.supplier_med_id
WHERE orderd.supplier_med_id = 3
AND orderd.expiration_date < '2019-11-05'

加入tbl_received_order_details和tbl_transaction_details使用supplier_med_id。

过滤具有药物 ID '3' 和过期日期的记录。

由于特定药物可能有超过 1 笔交易，因此我们需要将SUM已售出的数量从总数量中减去以得到剩余的数量。

更新 1

以上查询将获得已售出的过期药品。要获得所有已售库存的剩余数量，请使用GROUP BY.

SELECT orderd.expiration_date, orderd.rec_qty - SUM(trans.qty) AS inventory_left
FROM tbl_received_order_details AS orderd
JOIN tbl_transaction_details AS trans
ON orderd.supplier_med_id = trans.supplier_med_id
WHERE orderd.expiration_date < '2019-11-05'
GROUP BY orderd.supplier_med_id

更新 2

要获取所有已过期的剩余库存，无论它们是否已售出，请使用LEFT JOIN. COALESCE(trans.qty, 0)将返回第一个NOT NULL值。因此，如果一种药物从未售出，它在交易表中将没有任何条目，该条目将返回 qty 为 NULL。在这种情况下，我们将减去零rec_qty，然后将得到完整的 rec_qty 作为剩余部分。

SELECT orderd.expiration_date, orderd.rec_qty - SUM(COALESCE(trans.qty, 0)) AS inventory_left
FROM tbl_received_order_details AS orderd
LEFT JOIN tbl_transaction_details AS trans
ON orderd.supplier_med_id = trans.supplier_med_id
WHERE orderd.expiration_date < '2019-11-05'
GROUP BY orderd.supplier_med_id

推荐阅读