mysql - 如何获取已过期和尚未过期的数量?
问题描述
我将过期药品的数量减去实际库存。我用它(IFNULL(tbl_medicine.quantity - SUM(tbl_received.received_quantity),0)) AS Total
来DATE(NOW()) > tbl_received.expiration_date
获取过期药物的日期。我的问题是我无法获得尚未过期的数量。过期药品如何查询未过期的数量?有人可以帮我解决我的问题吗?这是我的查询...
SELECT tbl_med.sup_med_id, tbl_med.quantity,
tbl_received.received_quantity, tbl_med.status,
(IFNULL(tbl_med.quantity - SUM(tbl_received.received_quantity),0)) AS Total
FROM
tbl_med
INNER JOIN
tbl_received ON tbl_received.sup_med_id = tbl_med.sup_med_id
WHERE
tbl_med.status = 'Active' AND DATE(NOW()) > tbl_received.expiration_date AND
tbl_med.barangay_id = 19
GROUP BY
sup_med_id HAVING Total > 0
ORDER BY
sup_med_id
上面查询的输出
sup_med_id quantity received_quantity status Total
3 1800 1000 Active 800
7 1800 1000 Active 800
tbl_med & tbl_received 表
id sup_med_id received_quantity expiration_date
1 3 1000 2019-09-04
2 7 1000 2019-09-04
3 9 1800 2022-09-04
medicine_id sup_med_id quantity status barangay_id
1 3 1800 Active 19
2 7 1800 Active 19
3 9 1800 Active 19
我想让这发生...
sup_med_id quantity received_quantity status Total
3 1800 1000 Active 800
7 1800 1000 Active 800
9 1800 1800 Active 1800
如果药物尚未过期,我只想将数量显示到 Total
解决方案
问题来自您仅在到期日期过去时才加入的事实。为了解决这个问题,应该从WHERE
条款中删除检查并在SUM
过期数量中完成:
SELECT tbl_med.sup_med_id, tbl_med.quantity,
tbl_received.received_quantity, tbl_med.status,
(tbl_med.quantity
- SUM(CASE WHEN NOW() > tbl_received.expiration_date
THEN tbl_received.received_quantity
ELSE 0
END)
) AS Total
FROM
tbl_med
INNER JOIN
tbl_received ON tbl_received.sup_med_id = tbl_med.sup_med_id
WHERE
tbl_med.status = 'Active' AND
tbl_med.barangay_id = 19
GROUP BY
sup_med_id HAVING Total > 0
ORDER BY
sup_med_id
我自己没有测试过查询,但我相信它应该能达到你想要的
推荐阅读
- three.js - How to make camera update on zoom
- c++ - 具有重载 << 运算符的简单模板类失败,“模板 ID 的使用无效”
- ruby-on-rails - Error Running IRB from Terminal on Mac - Can't find gem
- javascript - 如何在 webpack 配置中包含和使用 DefinePlugin?
- php - How does laravel know the unread notifications i not see any difference in the notifications table?
- javascript - 如何减少div的前z-index?
- c - C程序中的奇怪行为,分配内存后变量损坏
- php - 使用 cURL 在 api 中拒绝承载授权
- moqui - 通过电子邮件向订阅用户发送 NotificationMessage
- php - PHP Curl 获取数据