Order product id, name, unit, quantity, time
1, tables, wang, 10201, 4.1. 1
2, table, wang, 8201, 4.1. 1
Product structure of outbound
Order product id, name, unit, quantity, time
1, tables, wang, 10201, 4.1, 2
2, tables, xiao wang, 4.1, 6201 8
Question: I want to get the product inventory and how many, as well as relevant information
Order product id, name, unit, quantity, time
1, tables, wang, 2201, 4.1. 1
Please consult
CodePudding user response:
Establish an inventory list, each storage, outbound are updating it,That is, when 2014.1.1 warehousing:
Product id, name, quantity, update time
1, tables, 10201 4.1. 1
2, tables, 8201 4.1. 1
When 2014.1.2 outbound
Product id, name, quantity, update time
1, tables, 0201 4.1. 2
2, tables, 8201 4.1. 1
When 2014.1.8 outbound
Product id, name, quantity, update time
1, tables, 0201 4.1. 2
2, table, 2201, 4.1 8
CodePudding user response:
1, the product inventory is more time-consuming, laborious?{should consider to build a product (real-time) inventory table}
2, ORACLE query is as follows:
SELECT TA. The product id,
TA. The name,
TA. Order unit,
NVL (SUM (TA), 0) - NVL (SUM (TB. Number), 0) AS the inventory quantity
The FROM the storing table TA
Left the join product outbound table TB on TA. The product id=TB. The product id
AND TA. Name=TB. Name
Order units AND TA. Order unit=TB.
GROUP BY TA. The product id, TA. Name, TA. Order unit
CodePudding user response:
Outbound program need not I write, customers still use the original program outbound, I only responsible for the statistics, I need to statistics the number did not pick up the goods in the table for ins and outs of how many,1, tables, xiao wang, 2201 4.1. 1