Sales table
The Item CO order_qty shipped_qty balance_qty
A CO1 15 5 10
A CO2 15 0
A CO3 20 5 15
CO1 20 10 10 B
B CO4 30 0 30
Inventory list
The Item Inventory
A 20
50 B
To achieve such a query result:
The Item CO order_qty shipped_qty balance_qty Allocation
A CO1 15 5 10 10
A CO2 15 15 10 0
A CO3 20 5 15 0
CO1 20 10 10 10 B
B CO4 30 0 30 30
PS: sales table is according to the date of delivery is already sorted
SQl Server version has been achieved, the statement as follows,
The great god help me translate the MySql version
SQL Server edition statement:
Select the item
, co
Order_qty,
, shipped_qty
, balance_qty
The rowids
, (the case when temp_qty & gt;=0 then balance_qty
The else (case when lag (temp_qty) over (partition by item order by rowids) & lt;=0 then 0
The else lag (temp_qty) over (partition by item order by rowids) end) end) Allocation
The from (select Anderson, tem, co, order_qty, shipped_qty, balance_qty,
B.i nventory - sum (balance_qty) over (partition by Anderson, tem order by Anderson, d) temp_qty, Anderson d rowids
From a, b
Where Anderson, tem=b.i tem) a
Below is the MySql tables and data script,
# to create tables and data, MySql
The CREATE TABLE b (
ID int primary key auto_increment,
The ITem varchar (50),
The Inventory float
);
The CREATE TABLE a (
ID int primary key auto_increment,
The ITem varchar (50),
CO varchar (50),
Order_Qty float,
Shipped_qty float,
Balance_qty float
);
Insert into b (Item, Inventory) VALUES (' A ', 20);
Insert into b (Item, Inventory) VALUES (' b ', 50);
Insert into a (Item, CO, Order_Qty, shipped_qty, balance_qty) values (' a ', 'CO1, 15, 5, 10);
Insert into a (Item, CO, Order_Qty, shipped_qty, balance_qty) values (' a ', 'CO2, 15, 0, 15);
Insert into a (Item, CO, Order_Qty, shipped_qty, balance_qty) values (' a ', 'CO3, 20, 5, 15);
Insert into a (Item, CO, Order_Qty, shipped_qty, balance_qty) values (' B ', 'CO1, 20, 10, 10);
Insert into a (Item, CO, Order_Qty, shipped_qty, balance_qty) values (' B ', 'CO4, 30, 5, 30);
CodePudding user response:
You this directly transplant to mysql is not good, you no grammar does not support this statement or not compatible