sql递减语句

USE test
GO
 
-->生成表A
 
if object_id('A') is not null 
    drop table A
Go
Create table A([PO] nvarchar(4),[LOT] nvarchar(1),[Item] datetime,[VEND] nvarchar(4),[QTY] smallint,[UNIT] nvarchar(3),[DATE] datetime)
Insert into A
Select N'P001',N'A','4711',N'V001',1000,N'PCS','2012/12/12'
 
 
-->生成表B
 
if object_id('B') is not null 
    drop table B
Go
Create table B([GRN] nvarchar(4),[PO] nvarchar(4),[LOT] nvarchar(1),[Item] datetime,[VEND] nvarchar(4),[QTY1] smallint,[UNIT] nvarchar(3),[DATE1] datetime)
Insert into B
Select N'G001',N'P001',N'A','4711',N'V001',400,N'PCS','2013/01/07'
Union all Select N'G003',N'P001',N'A','4711',N'V001',220,N'PCS','2013/01/17'
 
 
 
select 
        B.GRN
        ,B.PO
        ,B.LOT
        ,B.Item
        ,B.VEND
        ,B.QTY1
        ,B.UNIT
        ,B.DATE1 
        ,A.QTY-ISNULL(SUM(o.QTY1),0) AS BAL
    from A
        INNER JOIN B ON A.PO=B.PO
        LEFT JOIN B AS o ON B.PO=o.PO AND B.DATE1>=o.DATE1
    GROUP BY    
        B.GRN
        ,B.PO
        ,B.LOT
        ,B.Item
        ,B.VEND
        ,B.QTY1
        ,B.UNIT
        ,B.DATE1 
        ,A.QTY
/*
GRN  PO   LOT  Item                    VEND QTY1   UNIT DATE1                   BAL
---- ---- ---- ----------------------- ---- ------ ---- ----------------------- -----------
G001 P001 A    4711-01-01 00:00:00.000 V001 400    PCS  2013-01-07 00:00:00.000 600
G003 P001 A    4711-01-01 00:00:00.000 V001 220    PCS  2013-01-17 00:00:00.000 380
*/