I have 2 tables article and batches. What a want is to retrieve only the batch numbers (quantity-sold) when someone buys stock. The stock quantity is stored in batches because of the production date FIFO.
article
id|number|name
------------------
5|14432|Chemical H01
6|14433|Chemical H33
7|14434|Chemical HA1/5
batches
id|article_id|stock|batch_number|production_date
------------------
1|5|100|#34456|2021-09-21
2|5|21|#34476|2021-09-22
3|5|200|#34470|2021-10-11
4|5|100|#34471|2021-10-10
5|6|501|#77633|2021-10-11
6|6|100|#77634|2021-12-20
7|9|755|#90223|2021-12-30
If someone wants to buy 110 stock from article (id) 5 I want to retrieve 2 batch numbers; 1,2. (starting from oldest production_date)
If someone wants to buy 200 stock from article (id) 5 I want to retrieve 3 batch numbers; 1,2,4. (starting from oldest production_date)
SELECT a.number, (
SELECT GROUP_CONCAT(batch_number SEPARATOR ', ')
FROM batches
WHERE article_id=a.id
) AS batch_number
FROM article AS a
WHERE a.id=5
How can this be accomplished?
CodePudding user response：
If your MySQL version supports the window function, you can try to use SUM window function to get the accumulation from stock each article_id which can compare from your logic buy stock, then use
Query #1
SELECT t2.number,
GROUP_CONCAT(batch_number SEPARATOR ', ')
FROM (
SELECT *,SUM(stock) OVER(PARTITION BY article_id ORDER BY production_date) accumulation
FROM batches
) t1 INNER JOIN article t2
ON t1.article_id = t2.id
WHERE t2.id = 5 AND (accumulation - stock) < 200 --buy stock
GROUP BY t2.number;
| number | GROUP_CONCAT(batch_number SEPARATOR ', ') |
|---|---|
| 14432 | #34456, #34476, #34471 |
If your MySQL version didn't support there is another way to do that.
using correlated-subquery get accumulation in a subquery then filter accumulation as your logic.
SELECT t2.number,
GROUP_CONCAT(batch_number SEPARATOR ', ')
FROM (
SELECT *,
(SELECT SUM(bb.stock) FROM batches bb WHERE bb.article_id = b.article_id AND bb.production_date < b.production_date) accumulation
FROM batches b
) t1 INNER JOIN article t2
ON t1.article_id = t2.id
WHERE t2.id = 5 AND coalesce(accumulation,0) < 200 --buy stock
GROUP BY t2.number;