sql - 库存库存先进先出

Question

我有一个返回数据库中所有可用股票的查询。我需要做一个存储过程来获取用户输入的具体商品的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

Answer 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)