有兩張表：銷售表和庫存表
銷售表
Item CO order_qty shipped_qty balance_qty
A CO1 15 5 10
A CO2 15 0 15
A CO3 20 5 15
B CO1 20 10 10
B CO4 30 0 30
庫存表
Item Inventory
A 20
B 50
想實作這樣的查詢結果：
Item CO order_qty shipped_qty balance_qty Allocation
A CO1 15 5 10 10
A CO2 15 0 15 10
A CO3 20 5 15 0
B CO1 20 10 10 10
B CO4 30 0 30 30
PS:銷售表是按照交貨期已經排好序的
SQl Server 版本已經實作， 陳述句如下。
大神幫忙翻譯一下，MySql版
SQL Server版本陳述句：
select item
, co
, order_qty
, shipped_qty
, balance_qty
, rowids
, (case when temp_qty >= 0 then balance_qty
else (case when lag(temp_qty) over(partition by item order by rowids) <= 0 then 0
else lag(temp_qty) over(partition by item order by rowids) end) end) Allocation
from (select a.item, co, order_qty, shipped_qty, balance_qty,
b.inventory - sum(balance_qty) over(partition by a.item order by a.id ) temp_qty, a.id rowids
from a, b
where a.item = b.item) a
下面是MySql表和資料腳本。
#創建表及資料。 MySql
CREATE TABLE b (
ID int primary key auto_increment,
ITem varchar(50) ,
Inventory float
);
CREATE TABLE a (
ID int primary key auto_increment,
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);
uj5u.com熱心網友回復：
你這個直接移植到mysql不就好了嗎，你這個陳述句沒有哪個語法不支持或者不兼容的啊轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62383.html
標籤：MySQL
下一篇：如何分組查詢求和？