计算先进先出简例

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-01-06 08:54:41

--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

--          Mar 29 2009 10:27:29

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------------------------------------------------

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[存货表]') IS NULL

    DROP TABLE [存货表]

GO

CREATE TABLE [存货表]([编码] NVARCHAR(10),[数量] INT,[单价] DECIMAL(18,1),[日期] DATETIME)

INSERT [存货表]

SELECT '01',5,2.2,N'2009-10-01' UNION ALL

SELECT '01',9,2.5,N'2009-10-01' UNION ALL

SELECT '01',12,2.1,N'2009-10-02' UNION ALL

SELECT '02',20,15.2,N'2009-10-20' UNION ALL

SELECT '02',16,12.8,N'2009-10-20'

GO

--SELECT * FROM [存货表]

 

 

--> 生成测试数据表:销售表

 

IF NOT OBJECT_ID('[销售表]') IS NULL

    DROP TABLE [销售表]

GO

CREATE TABLE [销售表]([编码] NVARCHAR(10),[销售数量] INT)

INSERT [销售表]

SELECT '01',8 UNION ALL

SELECT '02',30

GO

--SELECT * FROM [销售表]

 

-->SQL查询如下:

   

;with t as

(

    select rn=ROW_NUMBER()over(Order by 编码,日期),*      

    from 存货表

),t1 as

(

    select *,tmpsum=(select SUM(数量) from t where 编码=a.编码 and rn<=a.rn)

    from t a

)

update a set

    a.数量=a.数量-case

                  when  a.数量-(a.tmpsum-b.销售数量) <=0 then 0

                  when a.tmpsum-b.销售数量 <=0  then a.数量

              else a.数量-(a.tmpsum-b.销售数量)

            end

from t1 a

    join 销售表 b

       on a.编码=b.编码

     

select * from 存货表

/*

编码        数量         单价                                     日期

---------- ----------- --------------------------------------- -----------------------

01         0           2.2                                     2009-10-01 00:00:00.000

01         6           2.5                                     2009-10-01 00:00:00.000

01         12          2.1                                     2009-10-02 00:00:00.000

02         0           15.2                                    2009-10-20 00:00:00.000

02         6           12.8                                    2009-10-20 00:00:00.000

 

(5 行受影响)

*/

 

 

 

/*---------------------------------------------------

以下简例为查询实际应发数，以下测试数据s 表示数量,c 表示仓库。

假设某物料要出100个，求从各仓库分别应发出多少数量。

----------------------------------------------------*/

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb] ([id] [int],[s] [int],[c] [int])

INSERT INTO [tb]

SELECT '1','20','1' UNION ALL

SELECT '2','30','2' UNION ALL

SELECT '3','80','3' UNION ALL

SELECT '4','30','4'

 

--SELECT * FROM [tb]

 

-->SQL查询如下:

DECLARE @i INT = 100

 

SELECT id, s = CASE

                    WHEN l>0 THEN s

                    WHEN s+l<0 THEN 0

                    ELSE s+l

               END, c

FROM (

         SELECT *, @i-(

                    SELECT SUM(s)

                    FROM tb

                    WHERE id<= a.id

                ) l

         FROM tb a

     ) t

 

/*

id          s           c

----------- ----------- -----------

1           20          1

2           30          2

3           50          3

4           0           4

 

(4 行受影响)

*/