MySQL的搜索受影响的行
问题描述:
假设我有如下表:MySQL的搜索受影响的行
production(rec_ID, item_ID, date, qty)
usage(rec_ID, item_ID, date, qty)
inventory(item_ID, stock)
随着下面的示例数据:
Production
Rec_ID | item_ID | date | qty
1 | A | 2016-01-18 | 50
2 | A | 2016-01-19 | 50
3 | A | 2016-01-20 | 50
Usage
Rec_ID | item_ID | date | qty
100 | A | 2016-01-20 | 70
Inventory
item_ID| stock
A | 80
正如你所看到的,在生产任何东西会增加库存和Usage中的任何内容都会减少Inventory。现在我有80个库存A.我想在表格生产中搜索哪一行有剩余库存。规则是:早日生产将首先用完。
用法ID 100将从生产ID 1开始(70-50)。余下的20将取自产品ID 2.因此,剩余存货为:80(产品ID 2为30,产品ID 3为50)。如何根据库存库存获取生产表中受影响的行?我想这样的:
SELECT * FROM production ORDER BY date DESC
循环通过项目加起来的数量,直到它达到当前的库存水平。例如:
current_Stock = SELECT stock FROM Inventory WHERE item_ID='A'
production_List = SELECT * FROM production WHERE item_ID='A' ORDER BY date DESC
for each production in production_List
qty_total += production.qty
(add each production to arraylist or something)
if qty_total >= current_Stock
exit for
end if
next
(process affected rows...)
在纯SQL中有更好的方法吗?
答
此查询可能是一个解决方案
SELECT NULL AS Rec_ID,
qty,
NULL AS total,
`date`
FROM cc
WHERE (@total := 0)
UNION
SELECT Rec_ID, qty, @total := @total + qty AS total, `date`
FROM
(SELECT *
FROM Production
ORDER BY `date` DESC
WHERE item_ID='A') tmp
WHERE @total <
(SELECT stock
FROM Inventory
WHERE item_ID='A') ;
“纯” SQL并不总是像这样复杂的业务规则最好的答案。你可能写了一个存储过程来做到这一点,但考虑让这些东西卡在数据库而不是应用程序代码中的缺点。 – miken32