有两张表:销售表和库存表
销售表
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 30PS:销售表是按照交货期已经排好序的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);
销售表
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 30PS:销售表是按照交货期已经排好序的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);
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货