我有一个查询,返回数据库中的所有可用股票。一世
需要做一个存储过程来获取用户输入的特定项目的itemCode、batchNo、数量、价格。
----------------------------------------------
| id | itemCode | batchNo | availQty | price |
----------------------------------------------
| 1 | item_1 | 07292016| 5 | 5.50 |
| 2 | item_1 | 07312016| 10 | 5.50 |
| 3 | item_1 | 08012016| 2 | 6.00 |
我的问题是,如果用户输入了 6 个要购买的数量,我怎样才能得到结果的前 2 行以获得 6 个的总数量?
结果必须是:
07292016 --- 5
07312016 --- 1
最佳答案
我怀疑这是否有效并且查询很糟糕,但以下内容将为您提供所需的内容:
-- Create Test Data
create table #Items
(
id int not null primary key,
itemCode varchar(30) not null,
batchNumber varchar(30) not null,
availQty int not null,
price smallmoney not null
);
insert into #Items
values
(1, 'item_1', '07292016', 5, 5.50),
(2, 'item_1', '07312016', 10, 5.50),
(3, 'item_1', '08012016', 2, 6.00)
select
*
from
#Items
;
-- Set up required parameters
declare
@requiredItemCode varchar(30) = 'item_1',
@requiredQty int = 6
-- The query to get the required result
select
i.*,
case
when
@requiredQty -
isnull(
(
select
sum(availQty)
from
#Items i2
where
i2.itemCode = i.itemCode
and i2.id < i.Id
),
0) < i.availQty
then
@requiredQty -
isnull(
(
select
sum(availQty)
from
#Items i2
where
i2.itemCode = i.itemCode
and i2.id < i.Id
),
0)
else
i.availQty
end as qtyToTake
from
#Items i
where
i.ItemCode = @requiredItemCode
and
case
when
@requiredQty -
isnull(
(
select
sum(availQty)
from
#Items i2
where
i2.itemCode = i.itemCode
and i2.id < i.Id
),
0) < i.availQty
then
@requiredQty -
isnull(
(
select
sum(availQty)
from
#Items i2
where
i2.itemCode = i.itemCode
and i2.id < i.Id
),
0)
else
i.availQty
end > 0
-- Clean up test data
drop table #Items
输出:
id itemCode batchNumber availQty price
----------- -------- ----------- ----------- ---------------------
1 item_1 07292016 5 5.50
2 item_1 07312016 10 5.50
3 item_1 08012016 2 6.00
(3 row(s) affected)
id itemCode batchNumber availQty price qtyToTake
----------- -------- ----------- ----------- --------------------- -----------
1 item_1 07292016 5 5.50 5
2 item_1 07312016 10 5.50 1
(2 row(s) affected)
关于sql - 库存库存先进先出,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38716665/