求一个先进先出SQL

[qq_37693160]

求一个存储过程 sp_GetInventory

传入参数
@物料代码 ，
@需求量


表结构及数据
物料代码 入库日期 库存数量
001 2017-5-1 100
001 2017-6-1 200
001 2017-8-1 1000
002 2017-5-2 300

1、运行存储过程 sp_GetInventory '001',80
出结果：
物料代码 入库日期 库存数量
001 2017-5-1 80

2、运行存储过程 sp_GetInventory '001',180
出结果：
物料代码 入库日期 库存数量
001 2017-5-1 100
001 2017-6-1 80

3、运行存储过程 sp_GetInventory '001',2400
出结果（没有足够库存，返回0条结果集）：
物料代码 入库日期 库存数量

[中国风]

e.g.

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tab1') is null
	drop table Tab1
Go
Create table Tab1([物料代码] nvarchar(23),[入库日期] Date,[库存数量] int)
Insert Tab1
select N'001','2017-5-1',100 union all
select N'001','2017-6-1',200 union all
select N'001','2017-8-1',1000 union all
select N'002','2017-5-2',300
GO

ALTER PROC sp_GetInventory
(
  @物料代码 nvarchar(23),
  @需求量 INT
)
AS
WITH    CTET
          AS ( SELECT   * ,
                        ( SELECT    SUM([库存数量])
                          FROM      Tab1
                          WHERE     [物料代码] = t.[物料代码]
                                    AND [入库日期] <= t.[入库日期]
                                    AND [库存数量] > 0
                        ) AS SumQty
               FROM     Tab1 AS t
               WHERE    [物料代码] = @物料代码
                        AND [库存数量] > 0
             )
    SELECT  [物料代码],[入库日期],[库存数量]=CASE WHEN SumQty<=@需求量 THEN [库存数量] ELSE @需求量-SumQty+[库存数量] END 
	FROM CTET
    WHERE   SumQty - [库存数量] < @需求量;
GO
EXEC sp_GetInventory '001',180
GO
/*
物料代码	入库日期	库存数量
001	2017-05-01	100
001	2017-06-01	80*/

[中国风]

SQL2012可用以下方式，新建存储过程时把ALTER改为CREATE

ALTER PROC sp_GetInventory
(
  @物料代码 nvarchar(23),
  @需求量 INT
)
AS
WITH    CTET
          AS ( SELECT   * ,
                        SUM([库存数量])OVER(ORDER BY [入库日期] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS SumQty
               FROM     Tab1 AS t
               WHERE    [物料代码] = @物料代码
                        AND [库存数量] > 0
             )
    SELECT  [物料代码],[入库日期],[库存数量]=CASE WHEN SumQty<=@需求量 THEN [库存数量] ELSE @需求量-SumQty+[库存数量] END 
	FROM CTET
    WHERE   SumQty - [库存数量] < @需求量;
GO
EXEC sp_GetInventory '001',180
GO
/*
物料代码	入库日期	库存数量
001	2017-05-01	100
001	2017-06-01	80*/

[mywisdom88]

-- 借斑竹的思路,改为 SQL2000能运行的,用临时表代替 CET if not object_id(N'Tab1') is null drop table Tab1 Go Create table Tab1([物料代码] nvarchar(23),[入库日期] Datetime,[库存数量] int) Insert into Tab1 select N'001','2017-5-1',100 union all select N'001','2017-6-1',200 union all select N'001','2017-8-1',1000 union all select N'002','2017-5-2',300 -- select * from #Tab2 GO alter proc dbo.sp_GetInventory ( @物料 nvarchar(23), @需求 int ) as begin -- 把库存表生成1个带累计数字段[库存]的临时表 if not object_id(N'tempdb..#Tab2') is null drop table #Tab2 create table #Tab2([物料代码] nvarchar(23),[入库日期] Datetime,[库存数量] int,[库存] int) insert into #Tab2 select T0.[物料代码],T0.[入库日期],T0.[库存数量], (select sum(库存数量) from Tab1 T1 where T0.物料代码=T1.物料代码 and T1.入库日期<=T0.入库日期 ) as [库存数] from Tab1 T0 order by 物料代码 asc,入库日期 asc -- 查询 select [物料代码],[入库日期], [出库数量]=(case when [库存]< @需求 then [库存数量] else @需求 -[库存] + [库存数量] end) from #Tab2 where [物料代码]=@物料 and [入库日期]<=(select top 1 [入库日期] from #Tab2 where [库存] >=@需求 and [物料代码]=@物料) end go EXEC dbo.sp_GetInventory '001',800 GO