如何获取过期和未过期的数量？

Question

我从实际库存中减去过期药品的数量。我使用这个(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

Answer 1

问题来自于这样一个事实，即你只有在到期日期已经过去的时候才加入。为了解决这个问题，应该从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

我自己还没有测试过这个查询，但我相信它应该能达到您想要的效果